﻿using AutoMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System;
using System.Linq;
using System.Security.Cryptography;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Security;
using housingsafety.server.Snowflake;
using System.Collections.Generic;
using System.ComponentModel;
using Microsoft.EntityFrameworkCore;
using System.Transactions;
using System.Text;
using Dapper;
using Npgsql;
using System.Threading.Tasks;
using System.IO;
using NetTopologySuite.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 市级任务包管理
    /// </summary>
    public class CitySampleTaskController : BaseController<CitySampleTaskController>
    {
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmSurveyUnit> _surveyUnitRepository;
        private readonly IRepository<ZmCityTask> _taskRepository;
        private readonly IRepository<ZmCityTaskSample> _taskSampleRepository;
        private readonly IRepository<ZmCitySampleCheck> _sampleCheckRepository;
        private readonly IRepository<RiskCensusHouseYhpc> _houseRepository;
        private readonly IRepository<RiskCensusYhpcUpldFile> _houseFileRepository;
        private readonly IRepository<ZmDepartment> _departmentRepository;

        public CitySampleTaskController(IRepository<ZmUser> userRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , ILogger<CitySampleTaskController> logger
            , IRepository<ZmSystemLog> systemLogRepository
            , IRepository<ZmSurveyUnit> surveyUnitRepository
            , IRepository<ZmCityTask> taskRepository
            , IRepository<ZmCityTaskSample> taskSampleRepository
            , IRepository<ZmCitySampleCheck> sampleCheckRepository
            , IRepository<RiskCensusHouseYhpc> houseRepository
            , IRepository<RiskCensusYhpcUpldFile> houseFileRepository
            , IRepository<ZmDepartment> departmentRepository) : base(logger, systemLogRepository)
        {
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();
            _userRepository = userRepository;
            _surveyUnitRepository = surveyUnitRepository;
            _taskRepository = taskRepository;
            _taskSampleRepository = taskSampleRepository;
            _sampleCheckRepository = sampleCheckRepository;
            _houseRepository = houseRepository;
            _houseFileRepository = houseFileRepository;
            _departmentRepository = departmentRepository;
        }

        /// <summary>
        /// 动态矢量瓦片服务(支持编辑)
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="z"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/dynamic/vector/tiles")]
        public ActionResult GetIntelLayerVt([FromQuery] int x, [FromQuery] int y, [FromQuery] int z)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return BadRequest("user not login");
            }

            var tile = GetDynamicVectorTile(z, x, y);
            return File(tile, "application/vnd.mapbox-vector-tile");
        }

        [HttpGet("city-sample-tasks/dynamic/vector/GetGeoJsonTest")]
        public Object GetGeoJsonTest()
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return BadRequest("user not login");
            }
          string  sql = $@"SELECT json_build_object(
  'type', 'FeatureCollection',
  'crs', json_build_object(
    'type', 'name',
    'properties', json_build_object(
      'name', 'urn:ogc:def:crs:OGC:1.3:CRS84'
    )
  ),
  'features', json_agg(
    json_build_object(
      'type', 'Feature',
      'properties', json_build_object(
        'id', '111',
        'mag', 'mag',
        'time', '555',
        'felt', 'felt',
        'tsunami', 'tsunami'
      ),
      'geometry', json_build_object(
        'type', 'Point',
        'coordinates', ST_X(ST_Centroid(geom)) || ',' || ST_Y(ST_Centroid(geom)) || ',0.0'
      )
    )
  )
) AS geojson
FROM (SELECT geom FROM risk_census_house_yhpc WHERE id IN (SELECT house_id FROM zm_city_task_sample WHERE task_id IN (SELECT id FROM zm_city_task WHERE is_delete IS NULL AND zjf_type = '2'))) b;
 ";
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql);
            return  datas;
        }


        /// <summary>
        /// 动态矢量瓦片服务(支持编辑)
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="z"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/dynamic/vector/tiles_other")]
        public ActionResult GetIntelLayerVtother([FromQuery] int x, [FromQuery] int y, [FromQuery] int z)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return BadRequest("user not login");
            }

            var tile = GetDynamicVectorTile_other(z, x, y);
            return File(tile, "application/vnd.mapbox-vector-tile");
        }


        /// <summary>
        /// 经营性自建房隐患统计
        /// </summary>
        /// <param name="grouplevel"></param>
        /// <param name="region_code"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/dynamic/GetJyxYh")]
        public Object GetJyxYh([FromQuery] string grouplevel, [FromQuery] string region_code, [FromQuery] string type_zx, [FromQuery] string pczt)
        {
            try
            {
                if (type_zx == "0")
                {
                    type_zx = "";
                }
                else if (type_zx == "1")
                {
                    type_zx = "and fwyt='1' ";
                }
                else if (type_zx == "2")
                {
                    type_zx = "and fwyt='2' ";
                }
                else
                {
                    return new RespRet<string>(false, "房屋类型参数不正确");
                }

                if (pczt != "-1")
                {
                    pczt = $" and status ='{pczt}'";
                }
                else
                {
                    pczt = "";
                }

                if (string.IsNullOrWhiteSpace(grouplevel))
                {
                    return new RespRet<string>(false, "分组等级必须传递");
                }
                string sql = "";
                if (grouplevel == "0")
                {
                    sql = $@"SELECT xzqdm,region_name qx,
SUM(CASE WHEN 1=1 {type_zx} THEN 1 ELSE 0 END)  zjfzs,
sum(case when (aqyhcp='3' or aqyhcp='1')  {type_zx} then 1 else 0 end) yyhfw,
sum(case when (aqyhcp='1') {type_zx} then 1 else 0 end) yzyhfw,
sum(case when (aqyhcp='3') {type_zx} then 1 else 0 end) ybyhfw
FROM risk_census_house_yhpc yhpc,zm_department qx
WHERE yhpc.is_delete IS NULL and yhpc.xzqdm=qx.region_code {pczt}
GROUP BY xzqdm,qx.region_name  order by  xzqdm";
                }
                if (grouplevel == "1")
                {
                    if (!string.IsNullOrWhiteSpace(region_code))
                    {
                        region_code = $"and xzqdm='{region_code}'";
                    }
                    sql = $@"SELECT qx.region_name qx,town as xzqdm,town,jd.region_name jd,
SUM(CASE WHEN 1=1 {type_zx} THEN 1 ELSE 0 END)  zjfzs,
sum(case when (aqyhcp='3' or aqyhcp='1')   {type_zx} then 1 else 0 end) yyhfw,
sum(case when (aqyhcp='1')  {type_zx} then 1 else 0 end) yzyhfw,
sum(case when (aqyhcp='3')  {type_zx} then 1 else 0 end) ybyhfw
 FROM risk_census_house_yhpc yhpc, zm_department qx,zm_department jd
WHERE yhpc.is_delete IS NULL and yhpc.xzqdm = qx.region_code and yhpc.town = jd.region_code {region_code} {pczt}
GROUP BY xzqdm,qx.region_name,town,jd.region_name order by town";
                }

                if (grouplevel == "2")
                {
                    if (!string.IsNullOrWhiteSpace(region_code))
                    {
                        region_code = $"and town='{region_code}'";
                    }
                    sql = $@"SELECT village as xzqdm,qx.region_name qx,town,jd.region_name jd,village,cz.region_name cz,
SUM(CASE WHEN 1=1 {type_zx} THEN 1 ELSE 0 END)  zjfzs,
sum(case when (aqyhcp='3' or aqyhcp='1')  {type_zx} then 1 else 0 end) yyhfw,
sum(case when (aqyhcp='1'){type_zx} then 1 else 0 end) yzyhfw,
sum(case when (aqyhcp='3') {type_zx} then 1 else 0 end) ybyhfw
 FROM risk_census_house_yhpc yhpc, zm_department qx,zm_department jd, zm_department cz
   WHERE yhpc.is_delete IS NULL and yhpc.xzqdm = qx.region_code and yhpc.town = jd.region_code and cz.region_code = village  {region_code}{pczt}
GROUP BY xzqdm,qx.region_name,town,jd.region_name,village,cz.region_name order by village";
                }


                if (grouplevel == "3")
                {
                    if (!string.IsNullOrWhiteSpace(region_code))
                    {
                        region_code = $"and village='{region_code}'";
                    }
                    sql = $@"SELECT xzqdm,qx.region_name qx,town,jd.region_name jd,village,cz.region_name cz,
SUM(CASE WHEN 1=1 {type_zx} THEN 1 ELSE 0 END)  zjfzs,
sum(case when (aqyhcp='3' or aqyhcp='1')  {type_zx} then 1 else 0 end) yyhfw,
sum(case when (aqyhcp='1') {type_zx} then 1 else 0 end) yzyhfw,
sum(case when (aqyhcp='3') {type_zx} then 1 else 0 end) ybyhfw
 FROM risk_census_house_yhpc yhpc, zm_department qx,zm_department jd, zm_department cz
   WHERE yhpc.is_delete IS NULL and yhpc.xzqdm = qx.region_code and yhpc.town = jd.region_code and cz.region_code = village {region_code}{pczt}
GROUP BY xzqdm,qx.region_name,town,jd.region_name,village,cz.region_name order by village";
                }
                //防止注入
                if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
                {
                    return new RespRet<List<object>>(false, "不符合规范，禁止执行");
                }
                var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                return new RespRet<List<object>>(true, "Ok", datas);

            }
            catch (Exception ex)
            {
                return new RespRet<List<object>>(false, "参数不正确,请检查参数");
            }

        }  
        [HttpGet("admin/custom-function/GetJyxYhDetail_Import")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<List<object>>), 200)]
        [ProducesResponseType(403)]
        public object GetJyxYhDetail_Import([FromQuery] string groupbyLever, [FromQuery] string groupbyLever_code, [FromQuery] string groupbyLever_code_project,[FromQuery]string type_zx, [FromQuery] string pczt)
        {

            if (pczt != "-1")
            {
                pczt = $" and status ='{pczt}'";
            }
            else
            {
                pczt = "";
            }
            if (type_zx == "0")
            {
                type_zx = "";
            }
            else if (type_zx == "1")
            {
                type_zx = "and fwyt='1' ";
            }
            else if (type_zx == "2")
            {
                type_zx = "and fwyt='2' ";
            }
            else
            {
                return new RespRet<Page<object>>(false, "房屋类型参数不正确");
            }
            if (string.IsNullOrWhiteSpace(groupbyLever))
            {
                return new RespRet<Page<object>>(false, "分组等级必须传递");
            }
            if (string.IsNullOrWhiteSpace(groupbyLever_code_project))
            {
                return new RespRet<Page<object>>(false, "明细字段必须传递");
            }
            if (string.IsNullOrWhiteSpace(groupbyLever_code))
            {
                return new RespRet<Page<object>>(false, "行政区代码必须传递");
            }
            try
            {
                string sql = "";
                string sqlcount = "";
                if (groupbyLever == "0")
                {
                    if (groupbyLever_code_project == "zjfzs")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ORDER BY village";

                    }
                    if (groupbyLever_code_project == "yyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3')
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ORDER BY village ";

                    }
                    if (groupbyLever_code_project == "yzyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ORDER BY village ";

                    }
                    if (groupbyLever_code_project == "ybyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ORDER BY village ";
                    }
                }
                if (groupbyLever == "1")
                {
                    if (groupbyLever_code_project == "zjfzs")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ORDER BY village ";

                    }
                    if (groupbyLever_code_project == "yyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3')
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ORDER BY village ";
                    }
                    if (groupbyLever_code_project == "yzyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ORDER BY village ";

                    }
                    if (groupbyLever_code_project == "ybyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ORDER BY village  ";

                    }

                }
                if (groupbyLever == "2")
                {
                    if (groupbyLever_code_project == "zjfzs")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ORDER BY village ";
                    }
                    if (groupbyLever_code_project == "yyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3')
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ORDER BY village ";

                    }
                    if (groupbyLever_code_project == "yzyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ORDER BY village ";
                    }
                    if (groupbyLever_code_project == "ybyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name townname
,jd.region_name villagename,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ORDER BY village";
                    }
                }
                //防止注入
                if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
                {
                    return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
                }
                //防止注入
                if (sqlcount.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sqlcount.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sqlcount.Contains("update ", StringComparison.OrdinalIgnoreCase))
                {
                    return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
                }
                var datas = _dbContext.Database.GetDbConnection().Query<Import_Yhpc>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("Sheet1");
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("房屋类型");
                row.CreateCell(2).SetCellValue("行政区");
                row.CreateCell(3).SetCellValue("街道");
                row.CreateCell(4).SetCellValue("村");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("隐患类型");
                row.CreateCell(8).SetCellValue("排查状态");
                // 第二行 写数据
                int i = 1;
                foreach (var item in datas)
                {
                    row = sheet.CreateRow(i);
                    row.CreateCell(0).SetCellValue(i);
                    if (item.Fwyt == "1")
                    {
                        row.CreateCell(1).SetCellValue("经营性自建房");
                    }
                    else if (item.Fwyt == "2")
                    {
                        row.CreateCell(1).SetCellValue("其他自建房");
                    }
                    row.CreateCell(2).SetCellValue(item.xzqdmname);
                    row.CreateCell(3).SetCellValue(item.townname);
                    row.CreateCell(4).SetCellValue(item.villagename);
                    row.CreateCell(5).SetCellValue(item.Address);
                    row.CreateCell(6).SetCellValue(item.Fwbh);
                    if (item.Aqyhcp == "1")
                    {
                        row.CreateCell(7).SetCellValue("严重安全隐患");
                    }
                    else if (item.Aqyhcp == "2")
                    {
                        row.CreateCell(7).SetCellValue("未发现隐患");
                    }
                    else if (item.Aqyhcp == "3")
                    {
                        row.CreateCell(7).SetCellValue("一般安全隐患");
                    }

                    if (item.Status ==0)
                    {
                        row.CreateCell(8).SetCellValue("待排查");
                    }
                    else if (item.Status == 1)
                    {
                        row.CreateCell(8).SetCellValue("排查中");
                    }
                    else if (item.Status ==2)
                    {
                        row.CreateCell(8).SetCellValue("已排查");
                    }
                    i++;
                }
                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "自建房隐患房屋明细.xlsx");

            }
            catch (Exception ex)
            {
                return new RespRet<string>(false, "参数不正确");
            }
        }
        /// <summary>
        /// 经营性自建房隐患统计
        /// </summary>
        /// <param name="grouplevel"></param>
        /// <param name="region_code"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/dynamic/GetJyxYhDetail")]
        public RespRet<Page<object>> GetJyxYhDetail([FromQuery] string groupbyLever, [FromQuery] string groupbyLever_code, [FromQuery] string groupbyLever_code_project, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string type_zx, [FromQuery] string pczt)
        {


            if (pczt != "-1")
            {
                pczt = $" and status ='{pczt}'";
            }
            else
            {
                pczt = "";
            }
            if (type_zx == "0")
            {
                type_zx = "";
            }
            else if (type_zx == "1")
            {
                type_zx = "and fwyt='1' ";
            }
            else if (type_zx == "2")
            {
                type_zx = "and fwyt='2' ";
            }
            else
            {
                return new RespRet<Page<object>>(false, "房屋类型参数不正确");
            }

            if (string.IsNullOrWhiteSpace(groupbyLever))
            {
                return new RespRet<Page<object>>(false, "分组等级必须传递");
            }
            if (string.IsNullOrWhiteSpace(groupbyLever_code_project))
            {
                return new RespRet<Page<object>>(false, "明细字段必须传递");
            }
            if (string.IsNullOrWhiteSpace(groupbyLever_code))
            {
                return new RespRet<Page<object>>(false, "行政区代码必须传递");
            }
            try
            {
                string sql = "";
                string sqlcount = "";
                if (groupbyLever == "0")
                {
                    if (groupbyLever_code_project == "zjfzs")
                    {
                        sql = $@"select qx.region_name xzqdmname, cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and xzqdm='{groupbyLever_code}'{pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null  {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "yyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null  {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3')
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3') 
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "yzyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "ybyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and xzqdm='{groupbyLever_code}' {pczt} ";
                    }
                }
                if (groupbyLever == "1")
                {
                    if (groupbyLever_code_project == "zjfzs")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "yyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3')
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3') 
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "yzyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "ybyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and town='{groupbyLever_code}' {pczt} ";
                    }

                }
                if (groupbyLever == "2")
                {
                    if (groupbyLever_code_project == "zjfzs")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "yyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3')
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and (aqyhcp='1' or aqyhcp='3') 
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "yzyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='1'
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ";
                    }
                    if (groupbyLever_code_project == "ybyhfw")
                    {
                        sql = $@"select qx.region_name xzqdmname,cz.region_name villagename
,jd.region_name townname,yhpc.* from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ORDER BY village limit {pagesize} offset {(pageindex - 1) * pagesize}";
                        sqlcount = $@"select count(1) from risk_census_house_yhpc
yhpc,zm_department qx,zm_department jd,zm_department cz  where yhpc.is_delete
is null {type_zx} and yhpc.xzqdm=qx.region_code and yhpc.town=jd.region_code and aqyhcp='3'
and village=cz.region_code and village='{groupbyLever_code}' {pczt} ";
                    }
                }
                //防止注入
                if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
                {
                    return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
                }
                //防止注入
                if (sqlcount.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sqlcount.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sqlcount.Contains("update ", StringComparison.OrdinalIgnoreCase))
                {
                    return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
                }
                int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sqlcount);
                var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

            }
            catch (Exception ex)
            {
                return new RespRet<Page<object>>(false, "请检查参数后重试");
            }
        }
        /// <summary>
        /// 删除指定的一个抽取到的房屋
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/houses/check-result/Del")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<SampleHouseCheckDto>), 200)]
        public RespRet<SampleHouseCheckDto> DelSampleData([FromQuery] string id)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<SampleHouseCheckDto>(false, "Invalid Request");
            }
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet<SampleHouseCheckDto>(false, "id必须输入");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<SampleHouseCheckDto>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (user == null)
            {
                return new RespRet<SampleHouseCheckDto>(false, "没有找到用户");
            }
            var data = _dbContext.ZmCityTaskSample.Where(c => c.HouseId == id && c.IsDelete == null).FirstOrDefault();
            if (data != null)
            {
                data.DeleteBy = user.Number;
                data.DeleteOn = DateTime.Now;
                data.IsDelete = 1;
                data.DeleteIp = this.IpAddress();
                _dbContext.ZmCityTaskSample.Update(data);
                var a = _dbContext.SaveChanges();
                if (a > 0)
                {
                    return new RespRet<SampleHouseCheckDto>(true, "删除成功");
                }
                else
                {
                    return new RespRet<SampleHouseCheckDto>(true, "删除失败");
                }
            }
            return new RespRet<SampleHouseCheckDto>(false, "未找到数据,请刷新后重试");
        }

        public class AddSample

        {
            public List<string> house_id { get; set; }

            public string task_id { get; set; }
        }

        /// <summary>
        /// 新增房屋到抽样任务
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/houses/check-result/Add")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<string>), 200)]
        public RespRet<string> AddSampleData([FromQuery] string house_id, [FromQuery] string task_id)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<string>(false, "Invalid Request");
            }
            if (string.IsNullOrEmpty(task_id))
            {
                return new RespRet<string>(false, "任务id必须输入");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<string>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (user == null)
            {
                return new RespRet<string>(false, "没有找到用户");
            }
            if (string.IsNullOrWhiteSpace(house_id))

            {
                return new RespRet<string>(false, "房屋id必须传递");
            }
            //判断类型是否相同
            var task = _dbContext.ZmCityTask.Where(c => c.Id == task_id && c.IsDelete == null).FirstOrDefault();
            if (task == null)
            {
                return new RespRet<string>(false, "没有找到任务");
            }
            var exists = _dbContext.ZmCityTaskSample.Where(c => c.HouseId == house_id && c.TaskId == task_id && c.IsDelete == null).FirstOrDefault();
            if (exists == null)
            {
                var house = _houseRepository.FirstOrDefault(_ => _.Id == house_id && _.IsDelete == null);
                if (house == null)
                {
                    return new RespRet<string>(false, "house not exist");
                }
                if (house.Fwyt != (task.ZjfType == null ? null : task.ZjfType.ToString()))
                {
                    return new RespRet<string>(false, "房屋用途与任务类型不匹配,不能添加");
                }
                _dbContext.ZmCityTaskSample.Add(new ZmCityTaskSample { Id = Guid.NewGuid().ToString("N"), CreateBy = user.Uname, CreateIp = this.IpAddress(), CreateOn = DateTime.Now, HouseId = house_id, TaskId = task_id });
                if (_dbContext.SaveChanges() > 0)
                {
                    task.SampleCount = _dbContext.ZmCityTaskSample.Where(c => c.TaskId == task_id).Count();
                    _dbContext.ZmCityTask.Update(task);
                    _dbContext.SaveChanges();
                    return new RespRet<string>(true, "添加成功", "添加成功");
                }
                else
                {
                    return new RespRet<string>(false, "添加失败");
                }
            }
            return new RespRet<string>(false, "房屋已存在,不能添加");
        }

        /// <summary>
        /// 随机抽样任务
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("city-sample-tasks/sampling-random")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<long>), 200)]
        [ProducesResponseType(403)]
        public RespRet<long> RandomSampling([FromBody] TaskSamplingInput request)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<long>(false, "Invalid Request");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<long>(false, "user not logined");
            }

            //var count = _taskRepository.Count(u => string.Equals(u.Name, request.Name)
            //&& u.IsDelete != 1 && u.CreateBy == userNumber);
            //if (count > 0)
            //{
            //    return new RespRet<long>(false, "名称重复");
            //}

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<long>(false, "用户不存在");
            }

            var ipAddress = this.IpAddress();
            var taskId = SequentialGuid.SequentialGuidGenerator.Instance.NewGuid().ToString("N");
            var dataTask = new TaskSamplingDto();
            dataTask.TaskId = taskId;


            int zjftype = 1;
            string type = "";
            if (request.ZjfType.HasValue)
            {
                if (request.ZjfType.Value == 1)
                {
                    //zjftype = 1;
                    type = " fwyt='1' ";
                }
                else if (request.ZjfType.Value == 2)
                {
                    type = " (fwyt='2' or fwyt='' or fwyt is null) ";
                }
            }

            using (TransactionScope trans = new TransactionScope())
            {
                try
                {
                    // create extension "uuid-ossp";
                    var sbSampleSql = new StringBuilder();
                    sbSampleSql.Append($@"SELECT h.ID, h.town, h.village, t.house_id 
FROM risk_census_house_yhpc h left join zm_city_task_sample t on h.id = t.house_id 
WHERE {type} and h.status = 2 ");

                    if (request.StartCs.HasValue && !request.EndCs.HasValue)
                    {
                        sbSampleSql.Append($" and cs >= {request.StartCs.Value} ");
                    }
                    else if (request.EndCs.HasValue && !request.StartCs.HasValue)
                    {
                        sbSampleSql.Append($" and cs <= {request.EndCs.Value} ");
                    }
                    else if (request.StartCs.HasValue && request.EndCs.HasValue)
                    {
                        sbSampleSql.Append($" and (cs between {request.StartCs.Value} and {request.EndCs.Value}) ");
                    }

                    if (request.SampleAreaCodes == null || request.SampleAreaCodes.Count < 1)
                    {
                        if (request.SampleAreaCodes == null)
                        {
                            request.SampleAreaCodes = new List<SimpleDepartmentDto>();
                        }

                        if (!string.IsNullOrEmpty(user.District))
                        {
                            request.SampleAreaCodes.Add(JsonConvert.DeserializeObject<SimpleDepartmentDto>(user.District));
                        }

                        request.SampleAreaCodesJson = user.DistrictJson;
                    }

                    var codeWhere = Utilitys.GetLikeSqlWhere(JsonConvert.SerializeObject(request.SampleAreaCodes), "h");
                    if (!string.IsNullOrEmpty(codeWhere))
                    {
                        sbSampleSql.Append($" and {codeWhere} ");
                    }

                    if (request.SampleType.HasValue)
                    {
                        var aqyhcp = 0;
                        if (request.SampleType.Value == 0)
                        {
                            aqyhcp = 2;
                        }
                        else if (request.SampleType.Value == 1)
                        {
                            aqyhcp = 3;
                        }
                        else if (request.SampleType.Value == 2)
                        {
                            aqyhcp = 1;
                        }

                        sbSampleSql.Append(@$" and h.aqyhcp = '{aqyhcp}'");
                    }

                    var sbSql = new StringBuilder();
                    sbSql.Append($@"INSERT INTO zm_city_task_sample(ID, task_id, house_id, create_by, create_on,
modified_by,modified_on) 
SELECT uuid_generate_v4(),'{taskId}',t2.ID,'{userNumber}',now(),'{userNumber}',now() 
FROM 
(
SELECT * 
FROM ({sbSampleSql}) t1 where t1.house_id is null
) t2 
ORDER BY random() 
LIMIT  CEILING( 
(SELECT count(t1.id) FROM ({sbSampleSql} ) t1 WHERE t1.house_id is null ) * {request.SampleProportion / 100.0})
");

                    //FROM tasktemp TABLESAMPLE BERNOULLI( {request.SampleProportion} ); ");

                    var num = _dbContext.Database.ExecuteSqlRaw(sbSql.ToString());
                    if (num == 0)
                    {
                        return new RespRet<long>(false, "抽取不到数据");
                    }
                    dataTask.SampleCount = num;

                    var data = new ZmCityTask
                    {
                        Id = taskId,
                        Name = request.Name,
                        SurveyUnitId = request.SurveyUnitId,
                        SampleProportion = request.SampleProportion,
                        SampleAreaCode = JsonConvert.SerializeObject(request.SampleAreaCodes),
                        SampleAreaCodesJson = request.SampleAreaCodesJson,
                        SampleType = request.SampleType,
                        SampleCount = num,
                        CreateBy = userNumber,
                        CreateOn = DateTime.Now,
                        CreateIp = ipAddress,
                        ModifiedBy = userNumber,
                        ModifiedOn = DateTime.Now,
                        ModifiedIp = ipAddress,
                        ZjfType = request.ZjfType
                    };

                    _taskRepository.Insert(data);
                    _taskRepository.SaveChanges();

                    trans.Complete();
                }
                catch (Exception e)
                {
                    try
                    {
                        Transaction.Current.Rollback();
                    }
                    catch (Exception exception)
                    {
                        Console.WriteLine(exception);
                    }
                    return new RespRet<long>(false, "抽取失败");
                }
            }

            return new RespRet<long>(true, "Ok", dataTask.SampleCount);
        }

        /// <summary>
        /// 随机抽样任务_动态预计抽取【市级】
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("city-sample-tasks/sampling-random_count")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<long>), 200)]
        [ProducesResponseType(403)]
        public RespRet<TaskSamplingDto> RandomSampling_count([FromBody] TaskSamplingInput request)
        {

            if (!ModelState.IsValid)
            {
                return new RespRet<TaskSamplingDto>(false, "Invalid Request");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<TaskSamplingDto>(false, "user not logined");
            }

            //var count = _taskRepository.Count(u => string.Equals(u.Name, request.Name)
            //&& u.IsDelete != 1 && u.CreateBy == userNumber);
            //if (count > 0)
            //{
            //    return new RespRet<TaskSamplingDto>(false, "名称重复");
            //}

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<TaskSamplingDto>(false, "用户不存在");
            }

            var ipAddress = this.IpAddress();
            var taskId = SequentialGuid.SequentialGuidGenerator.Instance.NewGuid().ToString("N");
            var dataTask = new TaskSamplingDto();
            dataTask.TaskId = taskId;

            string type = "";
            int zjftype = 2;
            if (request.ZjfType.HasValue)
            {
                if (request.ZjfType.Value == 1)
                {
                    //zjftype = 1;
                    type = " fwyt='1' ";
                }
                else if (request.ZjfType.Value == 2)
                {
                    type = " (fwyt='2' or fwyt='' or fwyt is null) ";
                }
            }

            using (TransactionScope trans = new TransactionScope())
            {
                try
                {
                    // create extension "uuid-ossp";
                    var sbSampleSql = new StringBuilder();
                    sbSampleSql.Append($@"SELECT h.ID, h.town, h.village, t.house_id 
FROM risk_census_house_yhpc h left join (
        SELECT *
        FROM zm_city_task_sample
        WHERE is_delete IS NULL and task_id in(select id from zm_city_task where zjf_type='{request.ZjfType}' and is_delete is null)
    ) t on h.id = t.house_id 
WHERE {type} and h.status = 2 ");

                    if (request.StartCs.HasValue && !request.EndCs.HasValue)
                    {
                        sbSampleSql.Append($" and cs >= {request.StartCs.Value} ");
                    }
                    else if (request.EndCs.HasValue && !request.StartCs.HasValue)
                    {
                        sbSampleSql.Append($" and cs <= {request.EndCs.Value} ");
                    }
                    else if (request.StartCs.HasValue && request.EndCs.HasValue)
                    {
                        sbSampleSql.Append($" and (cs between {request.StartCs.Value} and {request.EndCs.Value}) ");
                    }

                    if (request.SampleAreaCodes == null || request.SampleAreaCodes.Count < 1)
                    {
                        if (request.SampleAreaCodes == null)
                        {
                            request.SampleAreaCodes = new List<SimpleDepartmentDto>();
                        }

                        if (!string.IsNullOrEmpty(user.District))
                        {
                            request.SampleAreaCodes.Add(JsonConvert.DeserializeObject<SimpleDepartmentDto>(user.District));
                        }

                        request.SampleAreaCodesJson = user.DistrictJson;
                    }

                    var codeWhere = Utilitys.GetLikeSqlWhere(JsonConvert.SerializeObject(request.SampleAreaCodes), "h");
                    if (!string.IsNullOrEmpty(codeWhere))
                    {
                        sbSampleSql.Append($" and {codeWhere} ");
                    }

                    if (request.SampleType.HasValue)
                    {
                        var aqyhcp = 0;
                        if (request.SampleType.Value == 0)
                        {
                            aqyhcp = 2;
                        }
                        else if (request.SampleType.Value == 1)
                        {
                            aqyhcp = 3;
                        }
                        else if (request.SampleType.Value == 2)
                        {
                            aqyhcp = 1;
                        }

                        sbSampleSql.Append(@$" and h.aqyhcp = '{aqyhcp}'");
                    }
                    //status 0待排查1排查中2已排查
                    string strsql = $@"  SELECT count(t1.id) FROM({ sbSampleSql} ) t1 WHERE t1.house_id is null ";                    
                    var num_zl = _dbContext.Database.GetDbConnection().Query<long>(strsql).First();
                    string status0 = strsql.Replace("status = 2 ", "status = 0 ");
                    var num_status0 = _dbContext.Database.GetDbConnection().Query<long>(status0).First();
                    string status1 = strsql.Replace("status = 2 ", "status = 1 ");
                    var num_status1 = _dbContext.Database.GetDbConnection().Query<long>(status1).First();
                    var num_yj = (int)Math.Ceiling(num_zl * (request.SampleProportion / 100.0));
                    //_dbContext.Database.GetDbConnection().Query<int>
                    dataTask.SampleCount_yj = num_yj;
                    dataTask.SampleCount_zl = num_zl;
                    dataTask.Count_status0 = num_status0;
                    dataTask.Count_status1 = num_status1;
                    dataTask.Count_status2 = num_zl;
                    //dataTask.Msg = @$"预计抽样【{num_yj}】个图斑，共检索到【{num_zl}】个图斑";
                    dataTask.Msg = @$"已排查总数{num_zl}(待排查{num_status0}排查中{num_status1}),预计抽样{num_yj}";


                    trans.Complete();
                }
                catch (Exception e)
                {
                    try
                    {
                        Transaction.Current.Rollback();
                    }
                    catch (Exception exception)
                    {
                        Console.WriteLine(exception);
                    }
                    return new RespRet<TaskSamplingDto>(false, "计算失败");
                }
            }

            return new RespRet<TaskSamplingDto>(true, "Ok", dataTask);
        }

        /// <summary>
        /// 手动抽样任务
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("city-sample-tasks/sampling-manual")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<long>), 200)]
        [ProducesResponseType(403)]
        public RespRet<long> ManualSampling([FromBody] TaskManualSamplingInput request)
        {
            if (!ModelState.IsValid || request.FwbhArray == null || request.FwbhArray.Count < 1)
            {
                return new RespRet<long>(false, "Invalid Request");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<long>(false, "user not logined");
            }

            var count = _taskRepository.Count(u => string.Equals(u.Name, request.Name)
            && u.IsDelete != 1 && u.CreateBy == userNumber);
            if (count > 0)
            {
                return new RespRet<long>(false, "名称重复");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<long>(false, "用户不存在");
            }

            var ipAddress = this.IpAddress();
            var taskId = SequentialGuid.SequentialGuidGenerator.Instance.NewGuid().ToString("N");
            var dataTask = new TaskSamplingDto();
            dataTask.TaskId = taskId;
            int zjftype = 1;
            if (request.ZjfType.HasValue)
            {
                if (request.ZjfType.Value == 1)
                {
                    zjftype = 1;
                }
                else if (request.ZjfType.Value == 2)
                {
                    zjftype = 2;
                }
            }
            using (TransactionScope trans = new TransactionScope())
            {
                try
                {
                    // create extension "uuid-ossp";
                    var sbSampleSql = new StringBuilder();
                    sbSampleSql.Append($@"SELECT h.ID, h.town, h.village, t.house_id 
FROM risk_census_house_yhpc h left join zm_city_task_sample t on h.id = t.house_id 
WHERE h.fwyt = '{zjftype}' and h.status = 2 and h.fwbh in");

                    sbSampleSql.Append($@"(");
                    for (int i = 0; i < request.FwbhArray.Count; i++)
                    {
                        if (i == 0)
                        {
                            sbSampleSql.Append($@"'{request.FwbhArray[i]}'");
                            continue;
                        }

                        sbSampleSql.Append($@",'{request.FwbhArray[i]}'");
                    }
                    sbSampleSql.Append($@")");

                    var sbCountSql = new StringBuilder();
                    sbCountSql.Append($@"SELECT count(t1.id) FROM ({sbSampleSql} ) t1 WHERE t1.house_id is null");
                    var validFwbhList = _dbContext.Database.GetDbConnection()
                        .Query<long>(sbCountSql.ToString()).FirstOrDefault(); // 有效个房屋编号

                    var sbSql = new StringBuilder();
                    sbSql.Append($@"INSERT INTO zm_city_task_sample(ID, task_id, house_id, create_by, create_on,
modified_by,modified_on) 
SELECT uuid_generate_v4(),'{taskId}',t2.ID,'{userNumber}',now(),'{userNumber}',now() 
FROM 
(
SELECT * 
FROM ({sbSampleSql}) t1 where t1.house_id is null
) t2 ");

                    //FROM tasktemp TABLESAMPLE BERNOULLI( {request.SampleProportion} ); ");

                    var num = _dbContext.Database.ExecuteSqlRaw(sbSql.ToString());
                    if (num == 0)
                    {
                        return new RespRet<long>(false, "抽取不到数据");
                    }
                    dataTask.SampleCount = num;

                    var data = new ZmCityTask
                    {
                        Id = taskId,
                        Name = request.Name,
                        SurveyUnitId = request.SurveyUnitId,
                        SampleCount = num,
                        CreateBy = userNumber,
                        CreateOn = DateTime.Now,
                        CreateIp = ipAddress,
                        ModifiedBy = userNumber,
                        ModifiedOn = DateTime.Now,
                        ModifiedIp = ipAddress,
                        ZjfType = zjftype
                    };

                    _taskRepository.Insert(data);
                    _taskRepository.SaveChanges();

                    trans.Complete();
                }
                catch (Exception e)
                {
                    try
                    {
                        Transaction.Current.Rollback();
                    }
                    catch (Exception exception)
                    {
                        Console.WriteLine(exception);
                    }
                    return new RespRet<long>(false, "抽取失败");
                }
            }

            return new RespRet<long>(true, "Ok", dataTask.SampleCount);
        }

        /// <summary>
        /// 添加样品
        /// </summary>
        /// <param name="id"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("city-sample-tasks/{id}/samples/add")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<long>), 200)]
        [ProducesResponseType(403)]
        public RespRet<long> AddManualSampling([FromRoute] string id, [FromBody] AddTaskSampleInput request)
        {
            if (!ModelState.IsValid || request.FwbhArray == null || request.FwbhArray.Count < 1)
            {
                return new RespRet<long>(false, "Invalid Request");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<long>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<long>(false, "用户不存在");
            }

            var task = _taskRepository.FirstOrDefault(t => t.Id == id && t.IsDelete != 1);
            if (null == task)
            {
                return new RespRet<long>(false, "任务不存在");
            }

            var ipAddress = this.IpAddress();
            var dataTask = new TaskSamplingDto();
            dataTask.TaskId = task.Id;
            int zjftype = 1;
            if (request.ZjfType.HasValue)
            {
                if (request.ZjfType.Value == 1)
                {
                    zjftype = 1;
                }
                else if (request.ZjfType.Value == 2)
                {
                    zjftype = 2;
                }
            }
            using (TransactionScope trans = new TransactionScope())
            {
                try
                {
                    // create extension "uuid-ossp";
                    var sbSampleSql = new StringBuilder();
                    sbSampleSql.Append($@"SELECT h.ID, h.town, h.village, t.house_id 
            FROM risk_census_house_yhpc h left join zm_city_task_sample t on h.id = t.house_id 
            WHERE h.status = 2 and h.fwbh in");

                    sbSampleSql.Append($@"(");
                    for (int i = 0; i < request.FwbhArray.Count; i++)
                    {
                        if (i == 0)
                        {
                            sbSampleSql.Append($@"'{request.FwbhArray[i]}'");
                            continue;
                        }

                        sbSampleSql.Append($@",'{request.FwbhArray[i]}'");
                    }
                    sbSampleSql.Append($@")");

                    var sbCountSql = new StringBuilder();
                    sbCountSql.Append($@"SELECT count(t1.id) FROM ({sbSampleSql} ) t1 WHERE t1.house_id is null");
                    var validFwbhList = _dbContext.Database.GetDbConnection()
                        .Query<long>(sbCountSql.ToString()).FirstOrDefault(); // 有效个房屋编号
                    if (validFwbhList < 1)
                    {
                        return new RespRet<long>(false, "添加取样数据失败，可能已经被抽取");
                    }

                    var sbSql = new StringBuilder();
                    sbSql.Append($@"INSERT INTO zm_city_task_sample(ID, task_id, house_id, create_by, create_on,
            modified_by,modified_on) 
            SELECT uuid_generate_v4(),'{task.Id}',t2.ID,'{userNumber}',now(),'{userNumber}',now() 
            FROM 
            (
            SELECT * 
            FROM ({sbSampleSql}) t1 where t1.house_id is null
            ) t2 ");

                    var num = _dbContext.Database.ExecuteSqlRaw(sbSql.ToString());
                    if (num == 0)
                    {
                        return new RespRet<long>(false, "添加的数据无效");
                    }
                    dataTask.SampleCount = num;

                    task.SampleCount += num;
                    task.ModifiedBy = userNumber;
                    task.ModifiedOn = DateTime.Now;
                    task.ModifiedIp = ipAddress;
                    _taskRepository.Update(task);
                    _taskRepository.SaveChanges();

                    trans.Complete();
                }
                catch (Exception e)
                {
                    try
                    {
                        Transaction.Current.Rollback();
                    }
                    catch (Exception exception)
                    {
                        Console.WriteLine(exception);
                    }
                    return new RespRet<long>(false, "抽取失败");
                }
            }

            return new RespRet<long>(true, "Ok", dataTask.SampleCount);
        }

        /// <summary>
        /// 删除抽样样品
        /// </summary>
        /// <param name="id">任务ID</param>
        /// <param name="hid">房屋ID</param>
        /// <returns></returns>
        [HttpPost("city-sample-tasks/{id}/house-samples/{hid}/delete")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet), 200)]
        [ProducesResponseType(403)]
        public RespRet DeleteSample([FromRoute] string id, [FromRoute] string hid)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet(false, "userNumber not allow empty");
            }

            var adminUserNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(adminUserNumber))
            {
                return new RespRet(false, "user not logined");
            }

            // 先查询任务包里面的样品房屋是否核查
            var house = _houseRepository.FirstOrDefault(_ => _.Id == hid && _.CityResult != null);
            if (house != null)
            {
                return new RespRet(false, "此样品房屋已存在核查记录，不允许删除");
            }

            var task = _taskRepository.FirstOrDefault(t => t.Id == id && t.IsDelete != 1);
            if (null == task)
            {
                return new RespRet<long>(false, "任务不存在");
            }

            using (TransactionScope trans = new TransactionScope())
            {
                try
                {
                    task.SampleCount -= 1;
                    _taskRepository.Update(task);
                    _taskRepository.SaveChanges();

                    var a = _dbContext.Database.ExecuteSqlInterpolated($@"DELETE FROM zm_city_task_sample 
WHERE house_id={hid}");
                    if (a > 0)
                    {
                        trans.Complete();
                    }
                    else
                    {
                        Transaction.Current.Rollback();
                        return new RespRet(false, "删除失败");
                    }
                }
                catch (Exception e)
                {
                    try
                    {
                        Transaction.Current.Rollback();
                    }
                    catch (Exception exception)
                    {
                        Console.WriteLine(exception);
                    }
                    return new RespRet(false, "删除失败");
                }
            }

            return new RespRet(true, "Ok");
        }

        /// <summary>
        /// 获取抽样任务列表
        /// </summary>
        /// <param name="pageRequest">查询参数</param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/list")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<SampleTaskDto>>), 200)]
        [ProducesResponseType(403)]
        public RespRet<Page<SampleTaskDto>> List([FromQuery] PageTaskRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<SampleTaskDto>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<SampleTaskDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<SampleTaskDto>>(false, "用户不存在");
            }
            string getzql = ($@"UPDATE zm_city_task as t1 SET hc_acuracy = t2.hc_acuracy,hc_acuracycount=t2.hc_zq  FROM  v_zj_task_city AS t2 WHERE  t1.id = t2.id");
            var getzqlnum = _dbContext.Database.ExecuteSqlRaw(getzql.ToString());

            Page<SampleTaskDto> datas = null;
            var userDto = _mapper.Map<UserDto>(user);
            if (userDto.Role == PermissionRoleType.CityUnitAdmin
                || userDto.Role == PermissionRoleType.CityUnitInvestigator)
            {
                if (string.IsNullOrEmpty(pageRequest.Name))
                {
                    datas = (from task in _dbContext.ZmCityTask
                             join unit in _dbContext.ZmSurveyUnit on task.SurveyUnitId equals unit.Id into joinA
                             from b in joinA
                             where task.IsDelete != 1
                             where pageRequest.ZjfType > 0 ? task.ZjfType == pageRequest.ZjfType : true
                             where task.SurveyUnitId == user.SurveyUnitId
                             orderby task.ModifiedOn descending
                             select new SampleTaskDto
                             {
                                 Id = task.Id,
                                 Name = task.Name,
                                 SurveyUnitId = task.SurveyUnitId,
                                 SurveyUnitName = b.Name,
                                 SampleProportion = task.SampleProportion,
                                 SampleAreaCodes = task.SampleAreaCode != null ? JsonConvert
                                 .DeserializeObject<List<SimpleDepartmentDto>>(task.SampleAreaCode) : null,
                                 SampleAreaCodesJson = task.SampleAreaCodesJson,
                                 SampleType = task.SampleType,
                                 SampleCount = task.SampleCount,
                                 ModifiedOn = task.ModifiedOn,
                                 HcAcuracy = task.HcAcuracy,
                                 HcAcuracycount = task.HcAcuracycount,
                                 IsRandom = task.SampleProportion.HasValue,
                                 ZjfType = task.ZjfType
                             })
                        .ToPagedList(pageRequest.PageIndex + 1, pageRequest.Limit);
                }
                else
                {
                    datas = (from task in _dbContext.ZmCityTask
                             join unit in _dbContext.ZmSurveyUnit on task.SurveyUnitId equals unit.Id into joinA
                             from b in joinA
                             where task.IsDelete != 1
                             where pageRequest.ZjfType > 0 ? task.ZjfType == pageRequest.ZjfType : true
                             where task.Name.Contains(pageRequest.Name)
                             where task.SurveyUnitId == user.SurveyUnitId
                             orderby task.ModifiedOn descending
                             select new SampleTaskDto
                             {
                                 Id = task.Id,
                                 Name = task.Name,
                                 SurveyUnitId = task.SurveyUnitId,
                                 SurveyUnitName = b.Name,
                                 SampleProportion = task.SampleProportion,
                                 SampleAreaCodes = task.SampleAreaCode != null ? JsonConvert
                                 .DeserializeObject<List<SimpleDepartmentDto>>(task.SampleAreaCode) : null,
                                 SampleAreaCodesJson = task.SampleAreaCodesJson,
                                 SampleType = task.SampleType,
                                 SampleCount = task.SampleCount,
                                 ModifiedOn = task.ModifiedOn,
                                 HcAcuracy = task.HcAcuracy,
                                 HcAcuracycount = task.HcAcuracycount,
                                 IsRandom = task.SampleProportion.HasValue,
                                 ZjfType = task.ZjfType
                             })
                        .ToPagedList(pageRequest.PageIndex + 1, pageRequest.Limit);
                }
            }
            else if (userDto.Role == PermissionRoleType.City)
            {
                // 获取相同行政区、角色、组的管理员
                var groupFld = "\"group\"";
                var sameDistrictUser = _dbContext.ZmUser.FromSqlRaw($@"SELECT * FROM zm_user 
WHERE district->>'RegionCode' = '{userDto.District.RegionCode}' 
and role_type = {user.RoleType} and  {groupFld} = {user.Group}")?.Select(_ => _.Number)?.ToList();
                if (null == sameDistrictUser || sameDistrictUser.Count < 1)
                {
                    return new RespRet<Page<SampleTaskDto>>(false, "没有管理员");
                }

                if (string.IsNullOrEmpty(pageRequest.Name))
                {
                    if (string.IsNullOrEmpty(pageRequest.SurveyUnitName))
                    {
                        datas = (from task in _dbContext.ZmCityTask
                                 join unit in _dbContext.ZmSurveyUnit on task.SurveyUnitId equals unit.Id into joinA
                                 from b in joinA
                                 where pageRequest.ZjfType > 0 ? task.ZjfType == pageRequest.ZjfType : true
                                 where task.IsDelete != 1
                                 where sameDistrictUser.Contains(task.CreateBy)
                                 orderby task.ModifiedOn descending
                                 select new SampleTaskDto
                                 {
                                     Id = task.Id,
                                     Name = task.Name,
                                     SurveyUnitId = task.SurveyUnitId,
                                     SurveyUnitName = b.Name,
                                     SampleProportion = task.SampleProportion,
                                     SampleAreaCodes = task.SampleAreaCode != null ? JsonConvert
                                     .DeserializeObject<List<SimpleDepartmentDto>>(task.SampleAreaCode) : null,
                                     SampleAreaCodesJson = task.SampleAreaCodesJson,
                                     SampleType = task.SampleType,
                                     SampleCount = task.SampleCount,
                                     ModifiedOn = task.ModifiedOn,
                                     HcAcuracy = task.HcAcuracy,
                                     HcAcuracycount = task.HcAcuracycount,
                                     IsRandom = task.SampleProportion.HasValue,
                                     ZjfType = task.ZjfType
                                 })
                                 .ToPagedList(pageRequest.PageIndex + 1, pageRequest.Limit);
                    }
                    else
                    {
                        datas = (from task in _dbContext.ZmCityTask
                                 join unit in _dbContext.ZmSurveyUnit on task.SurveyUnitId equals unit.Id into joinA
                                 from b in joinA
                                 where pageRequest.ZjfType > 0 ? task.ZjfType == pageRequest.ZjfType : true
                                 where task.IsDelete != 1
                                 where b.Name.Contains(pageRequest.SurveyUnitName)
                                 where sameDistrictUser.Contains(task.CreateBy)
                                 orderby task.ModifiedOn descending
                                 select new SampleTaskDto
                                 {
                                     Id = task.Id,
                                     Name = task.Name,
                                     SurveyUnitId = task.SurveyUnitId,
                                     SurveyUnitName = b.Name,
                                     SampleProportion = task.SampleProportion,
                                     SampleAreaCodes = task.SampleAreaCode != null ? JsonConvert
                                     .DeserializeObject<List<SimpleDepartmentDto>>(task.SampleAreaCode) : null,
                                     SampleAreaCodesJson = task.SampleAreaCodesJson,
                                     SampleType = task.SampleType,
                                     SampleCount = task.SampleCount,
                                     ModifiedOn = task.ModifiedOn,
                                     HcAcuracy = task.HcAcuracy,
                                     HcAcuracycount = task.HcAcuracycount,
                                     IsRandom = task.SampleProportion.HasValue,
                                     ZjfType = task.ZjfType
                                 })
                                 .ToPagedList(pageRequest.PageIndex + 1, pageRequest.Limit);
                    }
                }
                else
                {
                    if (string.IsNullOrEmpty(pageRequest.SurveyUnitName))
                    {
                        datas = (from task in _dbContext.ZmCityTask
                                 join unit in _dbContext.ZmSurveyUnit on task.SurveyUnitId equals unit.Id into joinA
                                 from b in joinA
                                 where pageRequest.ZjfType > 0 ? task.ZjfType == pageRequest.ZjfType : true
                                 where task.IsDelete != 1
                                 where task.Name.Contains(pageRequest.Name)
                                 where sameDistrictUser.Contains(task.CreateBy)
                                 orderby task.ModifiedOn descending
                                 select new SampleTaskDto
                                 {
                                     Id = task.Id,
                                     Name = task.Name,
                                     SurveyUnitId = task.SurveyUnitId,
                                     SurveyUnitName = b.Name,
                                     SampleProportion = task.SampleProportion,
                                     SampleAreaCodes = task.SampleAreaCode != null ? JsonConvert
                                     .DeserializeObject<List<SimpleDepartmentDto>>(task.SampleAreaCode) : null,
                                     SampleAreaCodesJson = task.SampleAreaCodesJson,
                                     SampleType = task.SampleType,
                                     SampleCount = task.SampleCount,
                                     ModifiedOn = task.ModifiedOn,
                                     HcAcuracy = task.HcAcuracy,
                                     HcAcuracycount = task.HcAcuracycount,
                                     IsRandom = task.SampleProportion.HasValue,
                                     ZjfType = task.ZjfType
                                 })
                                 .ToPagedList(pageRequest.PageIndex + 1, pageRequest.Limit);
                    }
                    else
                    {
                        datas = (from task in _dbContext.ZmCityTask
                                 join unit in _dbContext.ZmSurveyUnit on task.SurveyUnitId equals unit.Id into joinA
                                 from b in joinA
                                 where pageRequest.ZjfType > 0 ? task.ZjfType == pageRequest.ZjfType : true
                                 where task.IsDelete != 1
                                 where task.Name.Contains(pageRequest.Name)
                                 where b.Name.Contains(pageRequest.SurveyUnitName)
                                 where sameDistrictUser.Contains(task.CreateBy)
                                 orderby task.ModifiedOn descending
                                 select new SampleTaskDto
                                 {
                                     Id = task.Id,
                                     Name = task.Name,
                                     SurveyUnitId = task.SurveyUnitId,
                                     SurveyUnitName = b.Name,
                                     SampleProportion = task.SampleProportion,
                                     SampleAreaCodes = task.SampleAreaCode != null ? JsonConvert
                                     .DeserializeObject<List<SimpleDepartmentDto>>(task.SampleAreaCode) : null,
                                     SampleAreaCodesJson = task.SampleAreaCodesJson,
                                     SampleType = task.SampleType,
                                     SampleCount = task.SampleCount,
                                     ModifiedOn = task.ModifiedOn,
                                     HcAcuracy = task.HcAcuracy,
                                     HcAcuracycount = task.HcAcuracycount,
                                     IsRandom = task.SampleProportion.HasValue,
                                     ZjfType = task.ZjfType
                                 })
                                 .ToPagedList(pageRequest.PageIndex + 1, pageRequest.Limit);
                    }
                }
            }

            return new RespRet<Page<SampleTaskDto>>(true, "Ok", datas);
        }

        /// <summary>
        /// 抽样统计
        /// </summary>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/stat")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<SampleTasksStatDto>), 200)]
        [ProducesResponseType(403)]
        public RespRet<SampleTasksStatDto> Stat([FromQuery] int zjfType=2)
        {
            // 等于2的房子才算自建房
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<SampleTasksStatDto>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<SampleTasksStatDto>(false, "用户不存在");
            }
            string type = "";
            //如果等于2
            if (zjfType == 2)
            {
                type = "  (fwyt is null or fwyt='' or fwyt='2') ";
            }
            else if (zjfType == 1)
            {
                type = " (fwyt ='1') ";
            }
            else
            {
                return new RespRet<SampleTasksStatDto>(false, "房屋类型不正确");
            }

            Page<SampleTaskDto> datas = null;
            var userDto = _mapper.Map<UserDto>(user);
            if (userDto.Role == PermissionRoleType.City)
            {
                // 获取相同行政区、角色、组的管理员
                var groupFld = "\"group\"";
                var sameDistrictUser = _dbContext.ZmUser.FromSqlRaw($@"SELECT * FROM zm_user 
WHERE district->>'RegionCode' = '{userDto.District.RegionCode}' 
and role_type = {user.RoleType} and  {groupFld} = {user.Group}")?.Select(_ => _.Number)?.ToList();
                if (null == sameDistrictUser || sameDistrictUser.Count < 1)
                {
                    return new RespRet<SampleTasksStatDto>(false, "没有管理员");
                }

                var sbSql = new StringBuilder();
                sbSql.Append($@"SELECT count(id) FROM risk_census_house_yhpc  
WHERE {type} and status = 2 ");

                var sampleAreaCodes = new List<SimpleDepartmentDto>();
                if (!string.IsNullOrEmpty(user.District))
                {
                    sampleAreaCodes.Add(JsonConvert.DeserializeObject<SimpleDepartmentDto>(user.District));
                }

                var codeWhere = Utilitys.GetLikeSqlWhere(JsonConvert.SerializeObject(sampleAreaCodes), null);
                if (!string.IsNullOrEmpty(codeWhere))
                {
                    sbSql.Append($" and {codeWhere} ");
                }

                var data = new SampleTasksStatDto();

                data.Total = _dbContext.Database.GetDbConnection().Query<long>(sbSql.ToString()).FirstOrDefault();

                string sql =@$"  SELECT count(1)  FROM(SELECT h.ID, h.town, h.village, t.house_id 
FROM risk_census_house_yhpc h left join (
        SELECT *
        FROM zm_city_task_sample
        WHERE is_delete IS NULL and task_id in(select id from zm_city_task where zjf_type='{zjfType}' and is_delete is null)
    ) t on h.id = t.house_id 
WHERE {type} and h.status = 2 and t.is_delete is null   ) t1 WHERE t1.house_id is not null ";             
                data.SampleTotal= _dbContext.Database.GetDbConnection().Query<long>(sql.ToString()).FirstOrDefault();

                //var bb = (from task in _dbContext.ZmCityTask
                //          where task.IsDelete != 1
                //          where task.SampleCount != null

                //          where task.ZjfType==1    
                //          where sameDistrictUser.Contains(task.CreateBy)
                //          select task.SampleCount).ToList();
                //data.SampleTotal = bb.Sum().Value;
                return new RespRet<SampleTasksStatDto>(true, "Ok", data);
            }

            return new RespRet<SampleTasksStatDto>(true, "Ok");
        }

        /// <summary>
        /// 导出任务包房屋数据到Excel
        /// </summary>
        /// <param name="id">任务ID</param>
        /// <param name="exportOption"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/{id}/datas/export")]
        [Produces("application/octet-stream")]
        [ProducesResponseType(200)]
        [ProducesResponseType(401)]
        public async Task<IActionResult> ExportExcel([FromRoute] string id, [FromQuery] ExportOption exportOption)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return Unauthorized(new RespRet(false, "user not login"));
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return Unauthorized(new RespRet(false, "user not login"));
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            sbSql.Append($@"SELECT a.* FROM risk_census_house_yhpc a 
left join zm_city_task_sample b on a.id=b.house_id 
WHERE b.task_id=@taskid ");
            sqlParams.Add(new NpgsqlParameter("taskid", id));

            if (!string.IsNullOrEmpty(exportOption.Address))
            {
                sbSqlWhere.Append($@" a.address like @address and");
                sqlParams.Add(new NpgsqlParameter("address", $"%{exportOption.Address}%"));
            }

            if (!string.IsNullOrEmpty(exportOption.Fwbh))
            {
                sbSqlWhere.Append($@" a.fwbh like @fwbh and");
                sqlParams.Add(new NpgsqlParameter("fwbh", $"%{exportOption.Fwbh}%"));
            }

            if (exportOption.Result.HasValue)
            {
                if (exportOption.Result.Value == -1)
                {
                    sbSqlWhere.Append($@" a.city_result is null and");
                }
                else
                {
                    sbSqlWhere.Append($@" a.city_result = @result and");
                    sqlParams.Add(new NpgsqlParameter("result", exportOption.Result.Value));
                }
            }

            if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
            {
                sbSql.Append($@" and {sbSqlWhere} 1=1 ");
            }

            var count = _dbContext.RiskCensusHouseYhpc.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).Count();
            if (count > 50000)
            {
                return BadRequest(new RespRet(false, "数据超过5w条，不允许导出"));
            }

            sbSql.Append($@" order by a.id");

            var datas = _dbContext.RiskCensusHouseYhpc.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray())
                    .Select(_ => _mapper.Map<CityTaskBuildingExportDto>(_)).ToList();

            var departments = _cache.Get("departments") as List<ZmDepartment>;
            if (null == departments)
            {
                departments = _departmentRepository.Query().ToList();
                _cache.Set("departments", departments);
            }

            foreach (var item in datas)
            {
                var find1 = departments.Find(_ => _.RegionCode == item.Xzqdm);
                item.XzqdmAlias = find1?.RegionName;
                var find2 = departments.Find(_ => _.RegionCode == item.Town);
                item.TownAlias = find2?.RegionName;
            }

            var fileName = Path.GetTempFileName();
            var mapper = new Npoi.Mapper.Mapper();
            mapper.Put(datas, "任务包房屋数据", true);
            mapper.Save(fileName);

            return await Task.FromResult(File(new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
                , "application/octet-stream"
                , $"dot_export_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"));
        }

        /// <summary>
        /// 获取任务包房屋属性列表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/{id}/search-attr")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<TaskBuildingAttrDto>>), 200)]
        public RespRet<Page<TaskBuildingAttrDto>> SearchAttrList([FromRoute] string id,
            [FromQuery] PageTaskHouseRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<TaskBuildingAttrDto>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<TaskBuildingAttrDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<TaskBuildingAttrDto>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            sbSql.Append($@"SELECT a.* FROM risk_census_house_yhpc a 
left join zm_city_task_sample b on a.id=b.house_id 
WHERE b.task_id=@taskid ");
            sqlParams.Add(new NpgsqlParameter("taskid", id));

            if (!string.IsNullOrEmpty(pageRequest.Text))
            {
                sbSqlWhere.Append($@" a.address like @address and");
                sqlParams.Add(new NpgsqlParameter("address", $"%{pageRequest.Text}%"));
            }

            if (!string.IsNullOrEmpty(pageRequest.Fwbh))
            {
                sbSqlWhere.Append($@" a.fwbh like @fwbh and");
                sqlParams.Add(new NpgsqlParameter("fwbh", $"%{pageRequest.Fwbh}%"));
            }

            if (pageRequest.Result.HasValue)
            {
                if (pageRequest.Result.Value == -1)
                {
                    sbSqlWhere.Append($@" a.city_result is null and");
                }
                else
                {
                    sbSqlWhere.Append($@" a.city_result = @result and");
                    sqlParams.Add(new NpgsqlParameter("result", pageRequest.Result.Value));
                }
            }

            if (!string.IsNullOrEmpty(sbSqlWhere.ToString()))
            {
                sbSql.Append($@" and {sbSqlWhere} 1=1 ");
            }

            var count = _dbContext.RiskCensusHouseYhpc.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).Count();

            sbSql.Append($@" order by a.id");
            sbSql.Append($@" limit @limit offset @offset");
            sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
            sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));

            var datas = _dbContext.RiskCensusHouseYhpc.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray())
                    .Select(_ => _mapper.Map<TaskBuildingAttrDto>(_)).ToList();

            var departments = _cache.Get("departments") as List<ZmDepartment>;
            if (null == departments)
            {
                departments = _departmentRepository.Query().ToList();
                _cache.Set("departments", departments);
            }

            foreach (var item in datas)
            {
                var find1 = departments.Find(_ => _.RegionCode == item.Xzqdm);
                item.XzqdmAlias = find1?.RegionName;
                var find2 = departments.Find(_ => _.RegionCode == item.Town);
                item.TownAlias = find2?.RegionName;
            }

            var pageData = new Page<TaskBuildingAttrDto>(datas
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<TaskBuildingAttrDto>>(true, "Ok", pageData);
        }

        /// <summary>
        /// 获取任务包房屋属性列表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/search-attr-Vector")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<TaskBuildingAttrDto>>), 200)]
        public ActionResult SearchAttrerVt([FromQuery] int x, [FromQuery] int y, [FromQuery] int z, [FromQuery] string id)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                return Unauthorized(new RespRet(false, "任务编号不能为空"));
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return BadRequest("user not login");
            }

            var tile = GetCheckTaskVT(z, x, y, id);
            return File(tile, "application/vnd.mapbox-vector-tile");
        }



        /// <summary>
        /// 获取未抽取的自建房数据(分类型)
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="z"></param>
        /// <param name="type">1经营性自建房，2非经营性自建房</param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/search-attr-Vector_yt")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<TaskBuildingAttrDto>>), 200)]
        public ActionResult SearchAttrerVt_allin([FromQuery] int x, [FromQuery] int y, [FromQuery] int z, [FromQuery] string type)
        {

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return BadRequest("user not login");
            }

            var tile = GetCheckTaskVT_allin(z, x, y, type);
            return File(tile, "application/vnd.mapbox-vector-tile");
        }

        /// <summary>
        /// 获取任务包房屋属性列表单个房屋
        /// </summary>
        /// <param name="build_no">房屋编号</param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/search-attr-fw")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<ZmYtBuild>), 200)]
        public new RespRet<TaskBuildingAttrDto> GetFw([FromQuery] string id)
        {

            if (string.IsNullOrEmpty(id))
            {
                return new RespRet<TaskBuildingAttrDto>(false, "房屋id不能为空");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<TaskBuildingAttrDto>(false, "user not logined");
            }

            var sbSql = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            sbSql.Append($@"SELECT * FROM risk_census_house_yhpc where is_delete is null and id=@id");
            sqlParams.Add(new NpgsqlParameter("id", id));

            var datas = _dbContext.RiskCensusHouseYhpc.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray())
                     .Select(_ => _mapper.Map<TaskBuildingAttrDto>(_)).FirstOrDefault();
            if (null == datas)
            {
                return null;
            }
            var xzqdm = $"select region_name From zm_department where region_code='{datas.Xzqdm}'";
            var town = $"select region_name From zm_department where region_code='{datas.Town}'";
            datas.TownAlias = _dbContext.Database.GetDbConnection().Query<string>(town).FirstOrDefault();
            datas.XzqdmAlias = _dbContext.Database.GetDbConnection().Query<string>(xzqdm).FirstOrDefault();
            return new RespRet<TaskBuildingAttrDto>(true, "ok", _mapper.Map<TaskBuildingAttrDto>(datas));
        }


        /// <summary>
        /// 获取任务包房屋的几何列表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="rect"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/{id}/search-geom")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<List<TaskBuildingGeomDto>>), 200)]
        [ProducesResponseType(403)]
        public RespRet<List<TaskBuildingGeomDto>> GetGeomList([FromRoute] string id, [FromQuery] ZmRect rect)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<List<TaskBuildingGeomDto>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<List<TaskBuildingGeomDto>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            sbSql.Append($@"SELECT st_asgeojson(a.geom) AS geometry, 
a.id,a.fwbh,a.yhfwbh,a.address, a.city_result as cityresult 
FROM risk_census_house_yhpc a left join zm_city_task_sample b on a.id=b.house_id 
WHERE geom && 
ST_SETSRID(ST_MakeBox2D(ST_MakePoint({rect.XMin}, {rect.YMin}),ST_MakePoint({rect.XMax}, {rect.YMax})),4326) 
AND b.task_id=@taskid");

            sqlParams.Add(new NpgsqlParameter("taskid", id));

            var datas = _dbContext.Database.GetDbConnection()
                   .Query<TaskBuildingGeomDto>(sbSql.ToString(), new { taskid = id }).ToList();
            return new RespRet<List<TaskBuildingGeomDto>>(true, "Ok", datas);
        }

        /// <summary>
        /// 获取自建房的调查信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/houses/{id}/info")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<TaskBuildForm>), 200)]
        public RespRet<TaskBuildForm> GetHousesById([FromRoute] string id)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet<TaskBuildForm>(false, "id can not be null");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<TaskBuildForm>(false, "user not logined");
            }

            var house =_dbContext.RiskCensusHouseYhpc.FirstOrDefault(_ => _.Id == id && _.IsDelete != 1);
            if (null == house)
            {
                return new RespRet<TaskBuildForm>(false, "house's id is not exist");
            }

            var data = new TaskBuildForm();
            data.Id = id;

            var form = new List<TaskBuildGroupForm>();

            #region 基本情况
            var group1 = new TaskBuildGroupForm();
            group1.Group = "基本情况";
            var data1 = new List<TaskBuildKeyValue>();
            data1.Add(new TaskBuildKeyValue
            {
                Key = "yhfwbh",
                Alias = "自建房安全排查编号",
                Value = house.Yhfwbh,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "fwbh",
                Alias = "普查房屋编号",
                Value = house.Fwbh,
                IsPhoto = false
            });

            var xzqdm = house.Xzqdm;
            if (!string.IsNullOrEmpty(house.Xzqdm))
            {
                var department = _departmentRepository.FirstOrDefault(_ => _.RegionCode == house.Xzqdm);
                if (null != department)
                {
                    xzqdm = department.RegionName;
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "xzqdm",
                Alias = "县（市、区、旗）",
                Value = xzqdm,
                IsPhoto = false
            });

            var town = house.Town;
            if (!string.IsNullOrEmpty(house.Town))
            {
                var department = _departmentRepository.FirstOrDefault(_ => _.RegionCode == house.Town);
                if (null != department)
                {
                    town = department.RegionName;
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "town",
                Alias = "乡（镇、街、道）",
                Value = town,
                IsPhoto = false
            });

            var village = house.Village;
            if (!string.IsNullOrEmpty(house.Village))
            {
                var department = _departmentRepository.FirstOrDefault(_ => _.RegionCode == house.Village);
                if (null != department)
                {
                    village = department.RegionName;
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "village",
                Alias = "村（社区）",
                Value = village,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "address",
                Alias = "建筑地址",
                Value = house.Address,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "zu_qg",
                Alias = "组",
                Value = house.ZuQg,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "lu_qg",
                Alias = "路（街、巷）",
                Value = house.LuQg,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "hao_qg",
                Alias = "号",
                Value = house.HaoQg,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "dong_qg",
                Alias = "栋",
                Value = house.DongQg,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "cqr",
                Alias = "产权人（使用人）姓名",
                Value = house.Cqr,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "sfzh",
                Alias = "身份证号",
                Value = house.Sfzh,
                IsPhoto = false
            });

            var fwyt = string.Empty;
            if (!string.IsNullOrEmpty(house.Fwyt))
            {
                if (house.Fwyt == "1")
                {
                    fwyt = "经营性自建房";
                }
                else if (house.Fwyt == "2")
                {
                    fwyt = "其他自建房";
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "fwyt",
                Alias = "房屋用途",
                Value = fwyt,
                IsPhoto = false
            });
            var jzgn = string.Empty;
            if (!string.IsNullOrEmpty(house.Jzgn))
            {
                var array = house.Jzgn.Split(",");
                for (int i = 0; i < array.Length; i++)
                {
                    var item = array[i];

                    if (i > 0)
                    {
                        jzgn += ",";
                    }

                    if (item == "1")
                    {
                        jzgn += "餐饮饭店";
                    }
                    else if (item == "2")
                    {
                        jzgn += "民宿宾馆";
                    }
                    else if (item == "3")
                    {
                        jzgn += "批发零售";
                    }
                    else if (item == "4")
                    {
                        jzgn += "医疗卫生";
                    }
                    else if (item == "5")
                    {
                        jzgn += "养老服务";
                    }
                    else if (item == "6")
                    {
                        jzgn += "生产加工";
                    }
                    else if (item == "7")
                    {
                        jzgn += "仓储物流";
                    }
                    else if (item == "8")
                    {
                        jzgn += "出租居住";
                    }
                    else if (item == "9")
                    {
                        jzgn += "休闲娱乐";
                    }
                    else if (item == "10")
                    {
                        jzgn += "教育培训";
                    }
                    else if (item == "11")
                    {
                        jzgn += "商务办公";
                    }
                    else if (item == "12")
                    {
                        jzgn += "农贸市场";
                    }
                    else if (item == "13")
                    {
                        jzgn += "宗教场所";
                    }
                    else if (item == "99")
                    {
                        jzgn += "其他";
                    }
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "jzgn",
                Alias = "具体用途",
                Value = jzgn,
                IsPhoto = false
            });
            data1.Add(new TaskBuildKeyValue
            {
                Key = "qtjzgn",
                Alias = "其他具体用途",
                Value = house.Qtjzgn,
                IsPhoto = false
            });

            var sub_fwyt = string.Empty;
            if (!string.IsNullOrEmpty(house.SubFwyt))
            {
                if (house.SubFwyt == "1")
                {
                    sub_fwyt = "是";
                }
                else if (house.SubFwyt == "0")
                {
                    sub_fwyt = "否";
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "sub_fwyt",
                Alias = "是否同时自住",
                Value = sub_fwyt,
                IsPhoto = false
            });

            var tdxz = string.Empty;
            if (!string.IsNullOrEmpty(house.Tdxz))
            {
                if (house.Tdxz == "1")
                {
                    tdxz = "国有土地";
                }
                else if (house.Tdxz == "2")
                {
                    tdxz = "集体土地";
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "tdxz",
                Alias = "土地性质",
                Value = tdxz,
                IsPhoto = false
            });
            var tdxzxflx = string.Empty;
            if (!string.IsNullOrEmpty(house.Tdxzxflx) && !string.IsNullOrEmpty(house.Tdxz))
            {
                if (house.Tdxz == "1")
                {
                    if (house.Tdxzxflx == "1")
                    {
                        tdxzxflx = "居住用地";
                    }
                    else if (house.Tdxzxflx == "2")
                    {
                        tdxzxflx = "商住用地";
                    }
                    else if (house.Tdxzxflx == "3")
                    {
                        tdxzxflx = "商业用地";
                    }
                    else if (house.Tdxzxflx == "4")
                    {
                        tdxzxflx = "国有非建设用地";
                    }
                    else if (house.Tdxzxflx == "99")
                    {
                        tdxzxflx = "其他";
                    }
                }
                else if (house.Tdxz == "2")
                {
                    if (house.Tdxzxflx == "1")
                    {
                        tdxzxflx = "宅基地";
                    }
                    else if (house.Tdxzxflx == "2")
                    {
                        tdxzxflx = "集体建设用地";
                    }
                    else if (house.Tdxzxflx == "3")
                    {
                        tdxzxflx = "集体非建设用地";
                    }
                    else if (house.Tdxzxflx == "99")
                    {
                        tdxzxflx = "其他";
                    }
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "tdxzxflx",
                Alias = "土地性质细分类型（国有土地/集体土地）",
                Value = tdxzxflx,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "qttdxzxflx",
                Alias = "其他土地性质细分类型",
                Value = house.Qttdxzxflx,
                IsPhoto = false
            });

            var szqy = string.Empty;
            if (!string.IsNullOrEmpty(house.Szqy))
            {
                if (house.Szqy == "1")
                {
                    szqy = "城市建成区";
                }
                else if (house.Szqy == "2")
                {
                    szqy = "建制镇";
                }
                else if (house.Szqy == "3")
                {
                    szqy = "集镇（乡镇政府所在地）";
                }
                else if (house.Szqy == "99")
                {
                    szqy = "其他";
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "szqy",
                Alias = "所在区域",
                Value = szqy,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "qtszqy",
                Alias = "其他所在区域",
                Value = house.Qtszqy,
                IsPhoto = false
            });

            var csjcqlx = string.Empty;
            if (!string.IsNullOrEmpty(house.Csjcqlx))
            {
                if (house.Csjcqlx == "1")
                {
                    csjcqlx = "城乡结合部";
                }
                else if (house.Csjcqlx == "2")
                {
                    csjcqlx = "城中村";
                }
                else if (house.Csjcqlx == "3")
                {
                    csjcqlx = "拆迁安置区";
                }
                else if (house.Csjcqlx == "4")
                {
                    csjcqlx = "工业园区";
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "csjcqlx",
                Alias = "城市建成区",
                Value = csjcqlx,
                IsPhoto = false
            });

            var rymjcslx = string.Empty;
            if (!string.IsNullOrEmpty(house.Rymjcslx))
            {
                var array = house.Rymjcslx.Split(",");
                for (int i = 0; i < array.Length; i++)
                {
                    var item = array[i];

                    if (i > 0)
                    {
                        rymjcslx += ",";
                    }
                    if (item == "1")
                    {
                        rymjcslx += "学校周边";
                    }
                    else if (item == "2")
                    {
                        rymjcslx += "医院周边";
                    }
                    else if (item == "3")
                    {
                        rymjcslx += "商贸市场周边";
                    }
                    else if (item == "99")
                    {
                        rymjcslx += "其他";
                    }
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "rymjcslx",
                Alias = "人员密集场所周边",
                Value = rymjcslx,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "qtrymjcslx",
                Alias = "其他人员密集场所",
                Value = house.Qtrymjcslx,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "cs",
                Alias = "建筑层数（层）",
                Value = house.Cs?.ToString(),
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "dcmj",
                Alias = "建筑面积（㎡）",
                Value = house.Dcmj?.ToString(),
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "jdqtfxbw",
                Alias = "建成时间",
                Value = house.Jdqtfxbw,
                IsPhoto = false
            });

            var jznd = string.Empty;
            if (!string.IsNullOrEmpty(house.Jznd))
            {
                if (house.Jznd == "1")
                {
                    jznd = "1980年及以前";
                }
                else if (house.Jznd == "2")
                {
                    jznd = "1981-1990年";
                }
                else if (house.Jznd == "3")
                {
                    jznd = "1991-2000年";
                }
                else if (house.Jznd == "4")
                {
                    jznd = "2001-2010年";
                }
                else if (house.Jznd == "5")
                {
                    jznd = "2011-2015年";
                }
                else if (house.Jznd == "6")
                {
                    jznd = "2016年及以后";
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "jznd",
                Alias = "建造年代",
                Value = jznd,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "build_time",
                Alias = "具体年份（年）",
                Value = house.BuildTime,
                IsPhoto = false
            });

            var pcqk = string.Empty;
            if (house.Pcqk.HasValue)
            {
                if (house.Pcqk == 0)
                {
                    pcqk = "不需要排查";
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "pcqk",
                Alias = "现场排查情况",
                Value = pcqk,
                IsPhoto = false
            });

            var bxypcyy = string.Empty;
            if (!string.IsNullOrEmpty(house.Bxypcyy))
            {
                if (house.Bxypcyy == "1")
                {
                    bxypcyy = "图斑为非房屋建筑";
                }
                else if (house.Bxypcyy == "2")
                {
                    bxypcyy = "其他不在本次排查内情况（需在备注中说明具体情况）";
                }
                else if (house.Bxypcyy == "3")
                {
                    bxypcyy = "非自建房";
                }
            }
            data1.Add(new TaskBuildKeyValue
            {
                Key = "bxypcyy",
                Alias = "不需要排查原因",
                Value = bxypcyy,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "qtbxypcyy",
                Alias = "备注",
                Value = house.Qtbxypcyy,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "pcr",
                Alias = "姓名",
                Value = house.Pcr,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "pcrjg",
                Alias = "所属单位",
                Value = house.Pcrjg,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "pcrdh",
                Alias = "联系电话",
                Value = house.Pcrdh,
                IsPhoto = false
            });

            data1.Add(new TaskBuildKeyValue
            {
                Key = "pcsj",
                Alias = "排查时间",
                Value = house.Pcsj?.ToString(),
                IsPhoto = false
            });
            group1.Data = data1;
            form.Add(group1);
            #endregion

            #region 建设情况
            var group2 = new TaskBuildGroupForm();
            group2.Group = "建设情况";
            var data2 = new List<TaskBuildKeyValue>();
            var sjfs = string.Empty;
            if (!string.IsNullOrEmpty(house.Sjfs))
            {
                if (house.Sjfs == "1")
                {
                    sjfs = "有专业设计（或采用标准图集）";
                }
                else if (house.Sjfs == "2")
                {
                    sjfs = "无专业设计（未采用标准图集）";
                }
            }
            data2.Add(new TaskBuildKeyValue
            {
                Key = "sjfs",
                Alias = "设计方式",
                Value = sjfs,
                IsPhoto = false
            });

            var sgdw = string.Empty;
            if (!string.IsNullOrEmpty(house.Sgdw))
            {
                if (house.Sgdw == "1")
                {
                    sgdw = "自行建造";
                }
                else if (house.Sgdw == "2")
                {
                    sgdw = "建筑工匠建造";
                }
                else if (house.Sgdw == "3")
                {
                    sgdw = "有资质的施工队伍建造";
                }
                else if (house.Sgdw == "99")
                {
                    sgdw = "其他";
                }
            }
            data2.Add(new TaskBuildKeyValue
            {
                Key = "sgdw",
                Alias = "建造方式",
                Value = sgdw,
                IsPhoto = false
            });

            data2.Add(new TaskBuildKeyValue
            {
                Key = "qtsgdw",
                Alias = "其他建造方式",
                Value = house.Qtsgdw,
                IsPhoto = false
            });

            var jglx = string.Empty;
            if (!string.IsNullOrEmpty(house.Jglx))
            {
                if (house.Jglx == "1")
                {
                    jglx = "砌体结构";
                }
                else if (house.Jglx == "2")
                {
                    jglx = "底部框架-上部砌体结构";
                }
                else if (house.Jglx == "3")
                {
                    jglx = "钢筋混凝土结构";
                }
                else if (house.Jglx == "4")
                {
                    jglx = "钢结构";
                }
                else if (house.Jglx == "5")
                {
                    jglx = "木（竹）结构";
                }
                else if (house.Jglx == "6")
                {
                    jglx = "窑洞";
                }
                else if (house.Jglx == "7")
                {
                    jglx = "混杂结构";
                }
                else if (house.Jglx == "8")
                {
                    jglx = "土木/石木结构";
                }
                else if (house.Jglx == "99")
                {
                    jglx = "其他";
                }
            }
            data2.Add(new TaskBuildKeyValue
            {
                Key = "jglx",
                Alias = "结构类型",
                Value = jglx,
                IsPhoto = false
            });

            var sub_jglx = string.Empty;
            if (!string.IsNullOrEmpty(house.SubJglx))
            {
                var array = house.SubJglx.Split(",");
                for (int i = 0; i < array.Length; i++)
                {
                    var item = array[i];

                    if (i > 0)
                    {
                        sub_jglx += ",";
                    }
                    if (item == "1")
                    {
                        sub_jglx += "预制板";
                    }
                    else if (item == "2")
                    {
                        sub_jglx += "现浇板";
                    }
                    else if (item == "3")
                    {
                        sub_jglx += "木或轻钢楼屋盖";
                    }
                    else if (item == "4")
                    {
                        sub_jglx += "石板或石条";
                    }
                }
            }
            data2.Add(new TaskBuildKeyValue
            {
                Key = "sub_jglx",
                Alias = "楼屋盖",
                Value = sub_jglx,
                IsPhoto = false
            });

            data2.Add(new TaskBuildKeyValue
            {
                Key = "qtjglx",
                Alias = "其他结构类型",
                Value = house.Qtjglx,
                IsPhoto = false
            });

            var sfgkj = string.Empty;
            if (!string.IsNullOrEmpty(house.Sfgkj))
            {
                if (house.Sfgkj == "1")
                {
                    sfgkj = "是";
                }
                else if (house.Sfgkj == "0")
                {
                    sfgkj = "否";
                }
            }
            data2.Add(new TaskBuildKeyValue
            {
                Key = "sfgkj",
                Alias = "是否改扩建",
                Value = sfgkj,
                IsPhoto = false
            });

            data2.Add(new TaskBuildKeyValue
            {
                Key = "gkjcs",
                Alias = "改扩建次数",
                Value = house.Gkjcs?.ToString(),
                IsPhoto = false
            });

            var gkjnr = string.Empty;
            if (!string.IsNullOrEmpty(house.Gkjnr))
            {
                var array = house.Gkjnr.Split(",");
                for (int i = 0; i < array.Length; i++)
                {
                    var item = array[i];

                    if (i > 0)
                    {
                        gkjnr += ",";
                    }

                    if (item == "1")
                    {
                        gkjnr += "加层";
                    }
                    else if (item == "2")
                    {
                        gkjnr += "加夹层";
                    }
                    else if (item == "3")
                    {
                        gkjnr += "加隔墙";
                    }
                    else if (item == "4")
                    {
                        gkjnr += "减柱";
                    }
                    else if (item == "5")
                    {
                        gkjnr += "减隔墙";
                    }
                    else if (item == "6")
                    {
                        gkjnr += "建筑外扩";
                    }
                    else if (item == "99")
                    {
                        gkjnr += "其他";
                    }
                }
            }
            data2.Add(new TaskBuildKeyValue
            {
                Key = "gkjnr",
                Alias = "改扩建内容",
                Value = gkjnr,
                IsPhoto = false
            });

            data2.Add(new TaskBuildKeyValue
            {
                Key = "qtgkjnr",
                Alias = "其他改扩建内容",
                Value = house.Qtgkjnr,
                IsPhoto = false
            });

            var sfzx = string.Empty;
            if (!string.IsNullOrEmpty(house.Sfzx))
            {
                if (house.Sfzx == "1")
                {
                    sfzx = "是";
                }
                else if (house.Sfzx == "0")
                {
                    sfzx = "否";
                }
            }
            data2.Add(new TaskBuildKeyValue
            {
                Key = "sfzx",
                Alias = "是否装修",
                Value = sfzx,
                IsPhoto = false
            });

            var zxsfkbztjg = string.Empty;
            if (!string.IsNullOrEmpty(house.Zxsfkbztjg))
            {
                if (house.Zxsfkbztjg == "1")
                {
                    zxsfkbztjg = "是";
                }
                else if (house.Zxsfkbztjg == "0")
                {
                    zxsfkbztjg = "否";
                }
            }
            data2.Add(new TaskBuildKeyValue
            {
                Key = "zxsfkbztjg",
                Alias = "装修是否改变主体结构",
                Value = zxsfkbztjg,
                IsPhoto = false
            });

            group2.Data = data2;
            form.Add(group2);
            #endregion

            #region 管理情况
            var group3 = new TaskBuildGroupForm();
            group3.Group = "管理情况";
            var data3 = new List<TaskBuildKeyValue>();
            var xzqksx = string.Empty;
            if (!string.IsNullOrEmpty(house.Xzqksx))
            {
                var array = house.Xzqksx.Split(",");
                for (int i = 0; i < array.Length; i++)
                {
                    var item = array[i];

                    if (i > 0)
                    {
                        xzqksx += ",";
                    }
                    if (item == "1")
                    {
                        xzqksx += "建设用地规划许可证";
                    }
                    else if (item == "2")
                    {
                        xzqksx += "国有土地使用权证";
                    }
                    else if (item == "3")
                    {
                        xzqksx += "集体建设用地使用权证";
                    }
                    else if (item == "4")
                    {
                        xzqksx += "宅基地批准书";
                    }
                    else if (item == "5")
                    {
                        xzqksx += "建设工程规划许可证";
                    }
                    else if (item == "6")
                    {
                        xzqksx += "施工许可证";
                    }
                    else if (item == "7")
                    {
                        xzqksx += "乡村建设规划许可证";
                    }
                    else if (item == "8")
                    {
                        xzqksx += "不动产登记";
                    }
                    else if (item == "9")
                    {
                        xzqksx += "市场主体登记（工商执照）";
                    }
                    else if (item == "10")
                    {
                        xzqksx += "经营许可证";
                    }
                    else if (item == "11")
                    {
                        xzqksx += "以上手续均无";
                    }
                }
            }
            data3.Add(new TaskBuildKeyValue
            {
                Key = "xzqksx",
                Alias = "已取得的行政许可",
                Value = xzqksx,
                IsPhoto = false
            });

            data3.Add(new TaskBuildKeyValue
            {
                Key = "jyxkhy",
                Alias = "经营许可行业",
                Value = house.Jyxkhy,
                IsPhoto = false
            });

            var sfwfjs = string.Empty;
            if (!string.IsNullOrEmpty(house.Sfwfjs))
            {
                if (house.Sfwfjs == "1")
                {
                    sfwfjs = "是";
                }
                else if (house.Sfwfjs == "0")
                {
                    sfwfjs = "否";
                }
            }
            data3.Add(new TaskBuildKeyValue
            {
                Key = "sfwfjs",
                Alias = "违法建设和违法审批",
                Value = sfwfjs,
                IsPhoto = false
            });

            var wfjsqk = string.Empty;
            if (!string.IsNullOrEmpty(house.Wfjsqk))
            {
                var array = house.Wfjsqk.Split(",");
                for (int i = 0; i < array.Length; i++)
                {
                    var item = array[i];

                    if (i > 0)
                    {
                        wfjsqk += ",";
                    }
                    if (item == "1")
                    {
                        wfjsqk += "违法占地";
                    }
                    else if (item == "2")
                    {
                        wfjsqk += "违法新建";
                    }
                    else if (item == "3")
                    {
                        wfjsqk += "违规改扩建";
                    }
                    else if (item == "4")
                    {
                        wfjsqk += "违规改变用途";
                    }
                }
            }
            data3.Add(new TaskBuildKeyValue
            {
                Key = "wfjsqk",
                Alias = "违法建设",
                Value = wfjsqk,
                IsPhoto = false
            });

            var wfspqk = string.Empty;
            if (!string.IsNullOrEmpty(house.Wfspqk))
            {
                var array = house.Wfspqk.Split(",");
                for (int i = 0; i < array.Length; i++)
                {
                    var item = array[i];

                    if (i > 0)
                    {
                        wfspqk += ",";
                    }
                    if (item == "1")
                    {
                        wfspqk += "违法用地审批";
                    }
                    else if (item == "2")
                    {
                        wfspqk += "违法规划审批";
                    }
                    else if (item == "3")
                    {
                        wfspqk += "违法建设审批";
                    }
                    else if (item == "4")
                    {
                        wfspqk += "违法经营许可";
                    }
                }
            }
            data3.Add(new TaskBuildKeyValue
            {
                Key = "wfspqk",
                Alias = "违法审批",
                Value = wfspqk,
                IsPhoto = false
            });

            group3.Data = data3;
            form.Add(group3);
            #endregion

            #region 排查情况
            var group4 = new TaskBuildGroupForm();
            group4.Group = "排查情况";
            var data4 = new List<TaskBuildKeyValue>();
            var bxss = string.Empty;
            if (!string.IsNullOrEmpty(house.Bxss))
            {
                if (house.Bxss == "1")
                {
                    bxss = "有变形损伤";
                }
                else if (house.Bxss == "0")
                {
                    bxss = "无变形损伤";
                }
            }
            data4.Add(new TaskBuildKeyValue
            {
                Key = "bxss",
                Alias = "结构状况",
                Value = bxss,
                IsPhoto = false
            });

            var bxssbw = string.Empty;
            if (!string.IsNullOrEmpty(house.Bxssbw))
            {
                var array = house.Bxssbw.Split(",");
                for (int i = 0; i < array.Length; i++)
                {
                    var item = array[i];

                    if (i > 0)
                    {
                        bxssbw += ",";
                    }
                    if (item == "1")
                    {
                        bxssbw += "墙体";
                    }
                    else if (item == "2")
                    {
                        bxssbw += "梁柱";
                    }
                    else if (item == "3")
                    {
                        bxssbw += "地基";
                    }
                    else if (item == "4")
                    {
                        bxssbw += "屋面";
                    }
                    else if (item == "5")
                    {
                        bxssbw += "楼板";
                    }
                    else if (item == "99")
                    {
                        bxssbw += "其他";
                    }
                }
            }
            data4.Add(new TaskBuildKeyValue
            {
                Key = "bxssbw",
                Alias = "具体部位",
                Value = bxssbw,
                IsPhoto = false
            });

            data4.Add(new TaskBuildKeyValue
            {
                Key = "qtbxssbw",
                Alias = "其他具体部位",
                Value = house.Qtbxssbw,
                IsPhoto = false
            });

            var aqyhcp = string.Empty;
            if (!string.IsNullOrEmpty(house.Aqyhcp))
            {
                if (house.Aqyhcp == "1")
                {
                    aqyhcp = "存在严重安全隐患";
                }
                else if (house.Aqyhcp == "3")
                {
                    aqyhcp = "存在一般安全隐患";
                }
                else if (house.Aqyhcp == "2")
                {
                    aqyhcp = "未发现安全隐患";
                }
            }
            data4.Add(new TaskBuildKeyValue
            {
                Key = "aqyhcp",
                Alias = "初步判定",
                Value = aqyhcp,
                IsPhoto = false
            });

            List<string> photos = null;
            if (!string.IsNullOrEmpty(house.Photo))
            {
                photos = _houseFileRepository.Query(_ => _.GroupId == house.Photo)?.Select(_ => _.FilePath)?.ToList();
            }

            data4.Add(new TaskBuildKeyValue
            {
                Key = "photo",
                Alias = "现场排查照片（1-10张）",
                Value = house.Photo,
                photosValue = photos,
                IsPhoto = true
            });

            group4.Data = data4;
            form.Add(group4);
            #endregion

            #region 整治情况
            var group5 = new TaskBuildGroupForm();
            group5.Group = "整治情况";
            var data5 = new List<TaskBuildKeyValue>();

            var sfaqjd = string.Empty;
            if (!string.IsNullOrEmpty(house.Sfaqjd))
            {
                if (house.Sfaqjd == "1")
                {
                    sfaqjd = "是";
                }
                else if (house.Sfaqjd == "0")
                {
                    sfaqjd = "否";
                }
            }
            data5.Add(new TaskBuildKeyValue
            {
                Key = "sfaqjd",
                Alias = "是否安全鉴定",
                Value = sfaqjd,
                IsPhoto = false
            });

            data5.Add(new TaskBuildKeyValue
            {
                Key = "jddwmc",
                Alias = "鉴定机构名称",
                Value = house.Jddwmc,
                IsPhoto = false
            });

            data5.Add(new TaskBuildKeyValue
            {
                Key = "tyssxydm",
                Alias = "统一社会信用代码",
                Value = house.Tyssxydm,
                IsPhoto = false
            });

            data5.Add(new TaskBuildKeyValue
            {
                Key = "jdsj",
                Alias = "鉴定时间",
                Value = house.Jdsj?.ToString(),
                IsPhoto = false
            });

            var fwaqdj = string.Empty;
            if (!string.IsNullOrEmpty(house.Fwaqdj))
            {
                if (house.Fwaqdj == "1")
                {
                    fwaqdj = "A级";
                }
                else if (house.Fwaqdj == "2")
                {
                    fwaqdj = "B级";
                }
                else if (house.Fwaqdj == "3")
                {
                    fwaqdj = "C级";
                }
                else if (house.Fwaqdj == "4")
                {
                    fwaqdj = "D级";
                }
            }
            data5.Add(new TaskBuildKeyValue
            {
                Key = "fwaqdj",
                Alias = "鉴定结论",
                Value = fwaqdj,
                IsPhoto = false
            });

            var glcs = string.Empty;
            if (!string.IsNullOrEmpty(house.Glcs))
            {
                if (house.Glcs == "1")
                {
                    glcs = "停止使用";
                }
                else if (house.Glcs == "2")
                {
                    glcs = "停止经营";
                }
                else if (house.Glcs == "3")
                {
                    glcs = "封控警示";
                }
                else if (house.Glcs == "4")
                {
                    glcs = "人员撤离";
                }
                else if (house.Glcs == "99")
                {
                    glcs = "其他";
                }
            }
            data5.Add(new TaskBuildKeyValue
            {
                Key = "glcs",
                Alias = "管理措施",
                Value = glcs,
                IsPhoto = false
            });

            data5.Add(new TaskBuildKeyValue
            {
                Key = "qtglcs",
                Alias = "其他管理措施",
                Value = house.Qtglcs,
                IsPhoto = false
            });

            var gccs = string.Empty;
            if (!string.IsNullOrEmpty(house.Gccs))
            {
                if (house.Gccs == "1")
                {
                    gccs = "拆除重建";
                }
                else if (house.Gccs == "2")
                {
                    gccs = "维修加固";
                }
                else if (house.Gccs == "99")
                {
                    gccs = "其他";
                }
            }
            data5.Add(new TaskBuildKeyValue
            {
                Key = "gccs",
                Alias = "工程措施",
                Value = gccs,
                IsPhoto = false
            });

            data5.Add(new TaskBuildKeyValue
            {
                Key = "qtgccs",
                Alias = "其他工程措施",
                Value = house.Qtgccs,
                IsPhoto = false
            });

            var wfjscc = string.Empty;
            if (!string.IsNullOrEmpty(house.Wfjscc))
            {
                if (house.Wfjscc == "1")
                {
                    wfjscc = "全部拆除";
                }
                else if (house.Wfjscc == "2")
                {
                    wfjscc = "部分拆除";
                }
                else if (house.Wfjscc == "3")
                {
                    wfjscc = "补办手续";
                }
                else if (house.Wfjscc == "4")
                {
                    wfjscc = "罚款";
                }
                else if (house.Wfjscc == "5")
                {
                    wfjscc = "没收";
                }
                else if (house.Wfjscc == "99")
                {
                    wfjscc = "其他";
                }
            }
            data5.Add(new TaskBuildKeyValue
            {
                Key = "wfjscc",
                Alias = "违法建设查处",
                Value = wfjscc,
                IsPhoto = false
            });

            data5.Add(new TaskBuildKeyValue
            {
                Key = "qtwfjscc",
                Alias = "其他违法建设查处",
                Value = house.Qtwfjscc,
                IsPhoto = false
            });

            List<string> zzPhotos = null;
            if (!string.IsNullOrEmpty(house.ZzPhoto))
            {
                zzPhotos = _houseFileRepository.Query(_ => _.GroupId == house.ZzPhoto)
                    ?.Select(_ => _.FilePath)?.ToList();
            }

            data5.Add(new TaskBuildKeyValue
            {
                Key = "zz_photo",
                Alias = "整治照片（1-10张）",
                Value = house.ZzPhoto,
                photosValue = zzPhotos,
                IsPhoto = true
            });

            group5.Data = data5;
            form.Add(group5);
            #endregion

            data.Form = form;
            return new RespRet<TaskBuildForm>(true, "Ok", data);
        }

        /// <summary>
        /// 获取自建房的最新核查信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/houses/{id}/check-result/last")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<SampleHouseCheckDto>), 200)]
        public RespRet<SampleHouseCheckDto> GetHousesCheckRstById([FromRoute] string id)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<SampleHouseCheckDto>(false, "Invalid Request");
            }

            if (string.IsNullOrEmpty(id))
            {
                return new RespRet<SampleHouseCheckDto>(false, "id必须输入");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<SampleHouseCheckDto>(false, "user not logined");
            }

            var datas = _sampleCheckRepository.Query(_ => _.HouseId == id && _.IsDelete != 1)?.ToList();
            if (null == datas || datas.Count < 1)
            {
                return new RespRet<SampleHouseCheckDto>(false, "没有核查记录");
            }

            var data = datas.OrderByDescending(_ => _.ModifiedOn).First();

            var rstData = _mapper.Map<SampleHouseCheckDto>(data);

            var user = _userRepository.FirstOrDefault(u => u.Number == data.ModifiedBy && u.IsDelete != 1);
            if (user != null)
            {
                rstData.ModifiedByName = user.Uname;
            }

            return new RespRet<SampleHouseCheckDto>(true, "Ok", rstData);
        }

        /// <summary>
        /// 获取自建房的任意核查信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="checkId"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/houses/{id}/check-result/{checkId}/info")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<SampleHouseCheckDto>), 200)]
        public RespRet<SampleHouseCheckDto> GetHousesCheckInfoById([FromRoute] string id, [FromRoute] string checkId)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<SampleHouseCheckDto>(false, "Invalid Request");
            }

            if (string.IsNullOrEmpty(id))
            {
                return new RespRet<SampleHouseCheckDto>(false, "id必须输入");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<SampleHouseCheckDto>(false, "user not logined");
            }

            var data = _sampleCheckRepository.FirstOrDefault(_ => _.HouseId == id
            && _.Id == checkId && _.IsDelete != 1);
            if (null == data)
            {
                return new RespRet<SampleHouseCheckDto>(false, "不存在");
            }

            var rstData = _mapper.Map<SampleHouseCheckDto>(data);

            var user = _userRepository.FirstOrDefault(u => u.Number == data.ModifiedBy && u.IsDelete != 1);
            if (user != null)
            {
                rstData.ModifiedByName = user.Uname;
            }

            return new RespRet<SampleHouseCheckDto>(true, "Ok", rstData);
        }

        /// <summary>
        /// 获取自建房的历史核查信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("city-sample-tasks/houses/{id}/check-result/history-list")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<List<SampleHouseCheckDto>>), 200)]
        public RespRet<List<SampleHouseCheckDto>> GetHousesCheckRstListById([FromRoute] string id)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<List<SampleHouseCheckDto>>(false, "Invalid Request");
            }

            if (string.IsNullOrEmpty(id))
            {
                return new RespRet<List<SampleHouseCheckDto>>(false, "id必须输入");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<List<SampleHouseCheckDto>>(false, "user not logined");
            }

            var datas = (from c in _dbContext.ZmCitySampleCheck
                         join user in _dbContext.ZmUser on c.ModifiedBy equals user.Number into joinA
                         from b in joinA
                         where c.IsDelete != 1
                         where c.HouseId == id
                         orderby c.ModifiedOn descending
                         select new SampleHouseCheckDto
                         {
                             Id = c.Id,
                             HouseId = c.HouseId,
                             Result = c.Result,
                             Note = c.Note,
                             PhotoIds = c.Photos != null
                             ? JsonConvert.DeserializeObject<List<string>>(c.Photos) : null,
                             ModifiedBy = c.ModifiedBy,
                             ModifiedByName = b.Uname,
                             ModifiedOn = c.ModifiedOn
                         })?.ToList();

            return new RespRet<List<SampleHouseCheckDto>>(true, "Ok", datas);
        }

        /// <summary>
        /// 更新自建房的核查信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("city-sample-tasks/houses/{id}/check-result/edit")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<string>), 200)]
        public RespRet<string> EditHousesCheckInfoById([FromRoute] string id, [FromBody] SampleHouseCheckInput request)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<string>(false, "Invalid Request");
            }

            if (string.IsNullOrEmpty(id))
            {
                return new RespRet<string>(false, "id必须输入");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<string>(false, "user not logined");
            }

            var house = _houseRepository.FirstOrDefault(_ => _.Id == id);
            if (house == null)
            {
                return new RespRet<string>(false, "house not exist");
            }

            var checkId = SequentialGuid.SequentialGuidGenerator.Instance.NewGuid().ToString("N");
            var data = new ZmCitySampleCheck
            {
                Id = checkId,
                HouseId = id,
                Result = request.Result,
                Note = request.Note,
                Photos = request.PhotoIds != null
                ? JsonConvert.SerializeObject(request.PhotoIds) : null,
                CreateBy = userNumber,
                CreateOn = DateTime.Now,
                CreateIp = this.IpAddress(),
                ModifiedBy = userNumber,
                ModifiedOn = DateTime.Now,
                ModifiedIp = this.IpAddress(),
                Btglx = request.Btglx,
                Ccjl = request.Ccjl,
                Ccsj = request.Ccsj,
                Aqyhrycc = request.Aqyhrycc
            };

            _sampleCheckRepository.Insert(data);
            _sampleCheckRepository.SaveChanges();

            house.CityResult = request.Result;
            house.CityBtglx = request.Btglx;
            house.Aqyhrycc = request.Aqyhrycc;
            house.Ccjl = request.Ccjl;
            house.Ccsj = request.Ccsj;
            _houseRepository.Update(house);
            _houseRepository.SaveChanges();
            return new RespRet<string>(true, "Ok", checkId);
        }

        /// <summary>
        /// 判断房屋是否可以添加到抽样任务
        /// </summary>
        /// <param name="id"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("city-sample-tasks/houses/check-result/Exists")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<string>), 200)]
        public RespRet<string> exists([FromQuery] string house_id, [FromQuery] string task_id)
        {
            if (!ModelState.IsValid)
            {
                return new RespRet<string>(false, "Invalid Request");
            }

            if (string.IsNullOrEmpty(house_id))
            {
                return new RespRet<string>(false, "必须指定房屋");
            }

            if (string.IsNullOrEmpty(task_id))
            {
                return new RespRet<string>(false, "必须指定任务");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<string>(false, "user not logined");
            }

            var task = _dbContext.ZmCityTask.Where(c => c.Id == task_id && c.IsDelete == null).FirstOrDefault();
            if (task == null)
            {
                return new RespRet<string>(false, "没有找到任务");
            }
            var exists = _dbContext.ZmCityTaskSample.Where(c => c.HouseId == house_id && c.TaskId == task_id && c.IsDelete == null).FirstOrDefault();
            if (exists == null)
            {
                var house = _houseRepository.FirstOrDefault(_ => _.Id == house_id && _.IsDelete == null);
                if (house == null)
                {
                    return new RespRet<string>(false, "house not exist");
                }
                if (house.Fwyt != (task.ZjfType == null ? null : task.ZjfType.ToString()))
                {
                    return new RespRet<string>(false, "房屋用途与任务类型不匹配,不能添加", "0");
                }
                return new RespRet<string>(false, "可以添加", "1");
            }

            return new RespRet<string>(true, "房屋已存在，可以删除", "2");
        }

        /// <summary>
        /// 删除抽样任务
        /// </summary>
        /// <param name="id">唯一编号</param>
        /// <returns></returns>
        [HttpPost("city-sample-tasks/{id}/delete")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet), 200)]
        [ProducesResponseType(403)]
        public RespRet Delete([FromRoute] string id)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new RespRet(false, "userNumber not allow empty");
            }

            var adminUserNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(adminUserNumber))
            {
                return new RespRet(false, "user not logined");
            }

            // 先查询任务包里面的房屋是否核查
            var sbSql = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();
            sbSql.Append($@"SELECT count(a.id) FROM risk_census_house_yhpc a 
left join zm_city_task_sample b on a.id=b.house_id 
WHERE b.task_id=@taskid and a.city_result is not null ");
            var count = _dbContext.Database.GetDbConnection()
                .Query<long>(sbSql.ToString(), new { taskid = id }).FirstOrDefault();
            if (count > 0)
            {
                return new RespRet(false, "任务包已存在核查记录，不允许删除");
            }

            using (TransactionScope trans = new TransactionScope())
            {
                try
                {
                    var data = _taskRepository.FirstOrDefault(u => u.Id == id && u.IsDelete != 1);
                    if (null == data)
                    {
                        return new RespRet(false, "不存在");
                    }

                    _taskRepository.Delete(data);
                    _taskRepository.SaveChanges();

                    _dbContext.Database.ExecuteSqlInterpolated($@"DELETE FROM zm_city_task_sample 
WHERE task_id={id}");

                    trans.Complete();
                }
                catch (Exception e)
                {
                    try
                    {
                        Transaction.Current.Rollback();
                    }
                    catch (Exception exception)
                    {
                        Console.WriteLine(exception);
                    }
                    return new RespRet(false, "删除失败");
                }
            }

            return new RespRet(true, "Ok");
        }

        private byte[] GetDynamicVectorTile(int z, int x, int y)
        {
            var sql = @$"SELECT 
ST_AsMVT(P, 'ffpolygon', 4096, 'geom') AS tile 
FROM 
(SELECT 
ST_AsMVTGeom( ST_Transform(ST_SetSRID(a.geom, 4326), 3857), ST_TileEnvelope( {z}, {x}, {y}),4096,256,FALSE) AS geom, 
a.id,a.fwbh,a.yhfwbh,a.address,a.city_result as result 
FROM risk_census_house_yhpc a,zm_city_task_sample b 
WHERE a.fwyt='1'and a.id=b.house_id and ST_Intersects(ST_SetSRID(a.geom, 4326), ST_Transform(ST_TileEnvelope( {z}, {x}, {y}), 4326))
) AS P";
            var tile = _dbContext.Database.GetDbConnection().Query<byte[]>(sql).FirstOrDefault();
            return tile;
        }


        private byte[] GetDynamicVectorTile_other(int z, int x, int y)
        {
            var sql = @$"SELECT 
ST_AsMVT(P, 'ffpolygon', 4096, 'geom') AS tile 
FROM 
(SELECT 
ST_AsMVTGeom( ST_Transform(ST_SetSRID(a.geom, 4326), 3857), ST_TileEnvelope( {z}, {x}, {y}),4096,256,FALSE) AS geom, 
a.id,a.fwbh,a.yhfwbh,a.address,a.city_result as result 
FROM risk_census_house_yhpc a,zm_city_task_sample b 
WHERE a.fwyt='2' and a.id=b.house_id and ST_Intersects(ST_SetSRID(a.geom, 4326), ST_Transform(ST_TileEnvelope( {z}, {x}, {y}), 4326))
) AS P";
            var tile = _dbContext.Database.GetDbConnection().Query<byte[]>(sql).FirstOrDefault();
            return tile;
        }
        private byte[] GetCheckTaskVT(int z, int x, int y, string id)
        {
            var sql = @$"SELECT 
ST_AsMVT(P, 'ffpolygon', 4096, 'geom') AS tile 
FROM 
(SELECT 
ST_AsMVTGeom( ST_Transform(ST_SetSRID(geom, 4326), 3857), ST_TileEnvelope( {z},{x},{y}),4096,256,FALSE) AS geom, 
risk_census_house_yhpc.*, (select region_name from zm_department where region_code=risk_census_house_yhpc.xzqdm) as xzqdmAlias,
(select region_name from zm_department where region_code=risk_census_house_yhpc.town) as townAlias
FROM risk_census_house_yhpc, zm_city_task_sample where risk_census_house_yhpc.id=zm_city_task_sample.house_id  and zm_city_task_sample.task_id='{id}'
and ST_Intersects(ST_SetSRID(geom, 4326), ST_Transform(ST_TileEnvelope( {z},{x},{y}), 4326))) AS P

";
            var tile = _dbContext.Database.GetDbConnection().Query<byte[]>(sql).FirstOrDefault();
            var tileString = Encoding.UTF8.GetString(tile);
            return tile;
        }



        private byte[] GetCheckTaskVT_allin(int z, int x, int y, string id)
        {
            var sql = @$"SELECT 
ST_AsMVT(P, 'ffpolygon', 4096, 'geom') AS tile 
FROM 
(SELECT 
ST_AsMVTGeom( ST_Transform(ST_SetSRID(geom, 4326), 3857), ST_TileEnvelope( {z},{x},{y}),4096,256,FALSE) AS geom, 
risk_census_house_yhpc.*
FROM risk_census_house_yhpc, zm_city_task_sample where risk_census_house_yhpc.id=zm_city_task_sample.house_id  and zm_city_task_sample.task_id='{id}'
and ST_Intersects(ST_SetSRID(geom, 4326), ST_Transform(ST_TileEnvelope( {z},{x},{y}), 4326))) AS P
";
            var tile = _dbContext.Database.GetDbConnection().Query<byte[]>(sql).FirstOrDefault();
            var tileString = Encoding.UTF8.GetString(tile);
            return tile;
        }
    }
}
